When you setup the appraisal templates in Core HR, that will be stored in "PER_APPRAISAL_TEMPLATES_V" table.
There are 3 temporary tables that hold data for entries made in SSHR until the approval cycle is complete
1) HR_API_TRANSACTIONS
2) HR_API_TRANSACTIONS_STEPS
3) HR_API_TRANSACTIONS_VALUES
Once the request is approved, data will be updated in "PER_PAY_PROPOSALS and PER_PERFORMANCE_REVIEWS_V" tables.
Below tables are interlinked:
PER_ALL_ASSIGNMENTS_F, PER_PAY_PROPOSALS and PER_PERFORMANCE_REVIEWS_V TABLES.
PER_ALL_ASSIGNMENTS_F
PER_ALL_PEOPLE_F
PER_JOBS
PER_GRADES
PER_APPRAISALS
PER_APPRAISAL_TEMPLATES
PER_ASSESSMENTS
PER_ASSESSMENT_GROUPS
PER_ASSESSMENT_TYPES
PER_COMPETENCES
PER_COMPETENCES_TL
PER_COMPETENCE_DEFINITIONS
PER_COMPETENCE_ELEMENTS
PER_PARTICIPANTS
PER_PERFORMANCE_RATINGS
PER_PERFORMANCE_REVIEWS
PER_RATING_LEVELS
PER_RATING_LEVELS_TL
PER_RATING_SCALES
PER_RATING_SCALES_TL
HR_QUESTIONNAIRES
HR_QUEST_ANSWERS
HR_QUEST_ANSWER_VALUES
HR_QUEST_FIELD
HR_ORGANIZATION_UNITS
PER_POSITIONS
---------------------------------------------------------------------------------------------------------------------------------------------
SELECT APPRAISAL_TYPE_M
,A.APPRAISEE
,C.EMPLOYEE_NUMBER
,A.MAIN_APPRAISER
,A.DEPARTMENT
,A.APPRAISAL_STATUS_M APPRAISAL_STATUS
, DECODE (B.APPRAISAL_SYSTEM_STATUS ,'ONGOING','Appraisal Pending With - '||A.MAIN_APPRAISER
,'APPRFEEDBACK','Appraisal Pending With - '||a.APPRAISEE
,'SAVED',' Appraisal Pending With - '||a.APPRAISEE
,'TRANSFER','Appraisal Pending With - '||a.APPRAISEE
,'COMPLETED', 'Appraisal Completed'
,'PENDINGAPPR','Appraisal Pending With - '||A.MAIN_APPRAISER
,B.APPRAISAL_SYSTEM_STATUS ) ownership
FROM APPS.HRFV_APPRAISAL_DETAILS A
,PER_APPRAISALS B
,PER_ALL_PEOPLE_F C
WHERE C.PERSON_ID = A.APPRAISEE_PERSON_ID
AND A.APPRAISAL_ID = B.APPRAISAL_ID
AND A.appraisal_id in ( select max(e.appraisal_id) from per_appraisals e
where e.plan_id is null
group by e.appraisee_person_id
having count(*) >= 1)
AND TRUNC (SYSDATE) BETWEEN TRUNC (C.effective_start_date)
AND TRUNC (C.effective_end_date)
order by A.APPRAISEE
SELECT bgrt.NAME business_group_name, orgt.NAME department,
apse.full_name appraisee, appr.full_name appraiser,
mapr.full_name main_appraiser, apr.appraisal_date appraisal_date,
apr.appraisal_period_start_date appraisal_start_date,
apr.appraisal_period_end_date appraisal_end_date,
apr.next_appraisal_date next_appraisal_date,
apr.comments appraisal_comments, rtt.NAME rating_level_name,
rtl.step_value step_value,
hr_bis.bis_decode_lookup ('APPRAISAL_TYPE',
apr.TYPE) appraisal_type,
hr_bis.bis_decode_lookup
('APPRAISAL_SYSTEM_STATUS',
apr.appraisal_system_status
) appraisal_status,
hr_bis.bis_decode_lookup ('APPRAISEE_ACCESS',
apr.appraisee_access
) appraisee_access,
apr.TYPE appraisal_type_code,
apr.appraisal_system_status appraisal_status_code,
apr.appraisee_access appraisee_access_code,
apr.creation_date creation_date,
apr.last_update_date last_update_date, '_DF:PER:PER_APPRAISALS:APR',
apr.appraisal_id appraisal_id,
apr.business_group_id business_group_id,
apr.appraisal_template_id appraisal_template_id,
apr.appraisee_person_id appraisee_person_id,
apr.appraiser_person_id appraiser_person_id,
apr.group_initiator_id group_initiator_id,
apr.overall_performance_level_id overall_performance_level_id,
apr.main_appraiser_id main_appraiser_id,
apr.assignment_id assignment_id, apr.event_id event_id,
rtl.rating_scale_id rating_scale_id,
rtl.competence_id competence_id,
orgt.organization_id organization_id
FROM per_appraisals apr,
hr_all_organization_units_tl bgrt,
hr_all_organization_units_tl orgt,
per_people_x apse,
per_people_x appr,
per_people_x mapr,
per_rating_levels rtl,
per_rating_levels_tl rtt
WHERE apr.business_group_id = bgrt.organization_id
AND bgrt.LANGUAGE = USERENV ('LANG')
AND apr.assignment_organization_id = orgt.organization_id
AND orgt.LANGUAGE = USERENV ('LANG')
AND apr.appraisee_person_id = apse.person_id
AND apr.appraiser_person_id = appr.person_id
AND apr.main_appraiser_id = mapr.person_id(+)
AND apr.overall_performance_level_id = rtl.rating_level_id(+)
AND rtl.rating_level_id = rtt.rating_level_id(+)
AND rtt.LANGUAGE(+) = USERENV ('LANG')
AND apr.business_group_id =
NVL (hr_bis.get_sec_profile_bg_id, apr.business_group_id)
WITH READ ONLY;
Friday, December 16, 2011
Wednesday, December 7, 2011
Confirm Receipts Workflow
Confirm Receipts workflow
Using Confirm Receipts, employees can view and receive orders in an extremely simple self service interface through Oracle Web Employees or by responding to workflow notifications. Confirm Receipts has the following major components:
A Confirm Receipts workflow process initiated after the due date of open orders
A series of web pages that enable employees to confirm receipt of orders and navigate to relevant documents
The Receiving Transaction Manager which fully validates the receipt information and completes the transaction in Purchasing
Confirm Receipts Workflow Process
The Confirm Receipt workflow process periodically polls for orders that are past due and sends workflow notifications to employees. The workflow engine drives a transaction through a process performing automated steps and invoking the appropriate agents when external processing is required. The engine monitors workflow states and coordinates routing of activities.
Confirm Receipts Notifications
The workflow engine polls for overdue orders and sends workflow notifications to the different users - typically the requester or the buyer. These notifications can be:
workflow generated notification (e.g. email)
viewed through a web browser in Oracle Web Employees
With Web-enabled notifications (email mail attachments or the inbox in Oracle Self-Service Web Applications), the user can drill down from the attached notification Web Page to the specific Receive Orders web page to view orders which are due and confirm receipt of these orders.
Confirm Receipts workflow generates notifications for an entire document if all items on that order have the same due date. If the document includes different due dates, the Confirm Receipts workflow generates a single notification per purchase order per requester per due date.
Review and Respond to Confirm Receipts Notifications
Oracle Web Employees automatically notifies employees of past due orders that have not been received. Valid responses to the notification include:
Fully Received You have fully received the items on the order.
Partially / Over Received You have received less than or more than the quantity of items you requested. In this case, you also drill down from the notification into the Receive Orders web page to record the receipt details. Oracle Web Employees automatically displays the open line items for this order. You can enter the quantity you have received.
Not Received You have not received the items included in the notification.
If the user fails to respond, it will be treated as a time out and a separate exception sub-process will be initiated.
Confirm Receipts Using "Receive Orders" Employee Self Service Web Pages
The user can confirm receipt of goods by using the Oracle Web Employees "Receive Orders" function or by responding to the workflow notification. Using the Receive Orders function in Oracle Web Employees, you can review all open orders that are past due.
However, if the page is accessed from a Confirm Receipts workflow notification, only order information associated with the notification will display on the page. Oracle Purchasing displays the unit of measure from the original purchase order or requisition.
Once you select the order lines you want to receive, press the "Submit" button. You can also identify which items you have partially or over-received, and then update the displayed quantity to reflect the quantity you have actually received.
Update Receiving in Oracle Purchasing
Once you confirm receipt of an order, either through the self-service Receive Orders web page, or by responding directly to the workflow notification, Oracle Web Employees automatically processes your receipt in Oracle Purchasing through the Receiving Transaction Processor.
The receiving transaction processor validates receipt transactions and updates the receiving history and purchase order tables in Oracle Purchasing. If the system encounters an error while processing the receiving transaction, notifications are sent to the requester and the buyer indicating the likely cause for failure.
Exception Handling
Oracle Web Employees handles the following exceptions as part of the Confirm Receipts workflow process:
Time Out Once a notification is sent, the process will be in a wait state to get a response from the user. If there is no response from the user, the system will treat it as a timeout and resend the notification after one week as a first reminder. If there is no response for another week, the notification is resent as a second reminder. If the user does not respond to the second reminder within twenty-four hours, a notification is sent to the user's manager.
Receiving Controls The receipt confirmation process enforces the receiving controls supported in Oracle Purchasing. For example, the system rejects received quantities that are greater than the tolerance level.
Configure the Receive Orders Web Page
Oracle Web Employees allows you to use the Web Applications Dictionary technology (a.k.a. Object Navigator) to tailor the layout and content of the Receive Orders self service web page. Use the Web Applications Manager to select the fields to display, the field, prompts, length, formatting characteristics, and include certain input fields as part of the receipt confirmation.
Setting Up Web Users
Each employee that will confirm receipts through Oracle Web Employees must be defined as a web user. To setup the Receive Orders Web Page for an employee you must:
Define a web user for employees who will receive orders through the web
Assign the Receive Orders functions to that user's list of responsibilities/functions
Respond to Receipt Confirmation Notifications
To respond to workflow generated receipt confirmation requests:
Login to Oracle Web Employees and select the "View Notifications" function.
Select a response to the receipt confirmation request:
Fully Received.
Over/Partially Received.
Not Received.
For partially/over received orders, drill down from the notification into the Receive Orders web page. Record your receipt details.
Select the "Submit Response" button to complete the response and initiate the next workflow activity.
Using Confirm Receipts, employees can view and receive orders in an extremely simple self service interface through Oracle Web Employees or by responding to workflow notifications. Confirm Receipts has the following major components:
A Confirm Receipts workflow process initiated after the due date of open orders
A series of web pages that enable employees to confirm receipt of orders and navigate to relevant documents
The Receiving Transaction Manager which fully validates the receipt information and completes the transaction in Purchasing
Confirm Receipts Workflow Process
The Confirm Receipt workflow process periodically polls for orders that are past due and sends workflow notifications to employees. The workflow engine drives a transaction through a process performing automated steps and invoking the appropriate agents when external processing is required. The engine monitors workflow states and coordinates routing of activities.
Confirm Receipts Notifications
The workflow engine polls for overdue orders and sends workflow notifications to the different users - typically the requester or the buyer. These notifications can be:
workflow generated notification (e.g. email)
viewed through a web browser in Oracle Web Employees
With Web-enabled notifications (email mail attachments or the inbox in Oracle Self-Service Web Applications), the user can drill down from the attached notification Web Page to the specific Receive Orders web page to view orders which are due and confirm receipt of these orders.
Confirm Receipts workflow generates notifications for an entire document if all items on that order have the same due date. If the document includes different due dates, the Confirm Receipts workflow generates a single notification per purchase order per requester per due date.
Review and Respond to Confirm Receipts Notifications
Oracle Web Employees automatically notifies employees of past due orders that have not been received. Valid responses to the notification include:
Fully Received You have fully received the items on the order.
Partially / Over Received You have received less than or more than the quantity of items you requested. In this case, you also drill down from the notification into the Receive Orders web page to record the receipt details. Oracle Web Employees automatically displays the open line items for this order. You can enter the quantity you have received.
Not Received You have not received the items included in the notification.
If the user fails to respond, it will be treated as a time out and a separate exception sub-process will be initiated.
Confirm Receipts Using "Receive Orders" Employee Self Service Web Pages
The user can confirm receipt of goods by using the Oracle Web Employees "Receive Orders" function or by responding to the workflow notification. Using the Receive Orders function in Oracle Web Employees, you can review all open orders that are past due.
However, if the page is accessed from a Confirm Receipts workflow notification, only order information associated with the notification will display on the page. Oracle Purchasing displays the unit of measure from the original purchase order or requisition.
Once you select the order lines you want to receive, press the "Submit" button. You can also identify which items you have partially or over-received, and then update the displayed quantity to reflect the quantity you have actually received.
Update Receiving in Oracle Purchasing
Once you confirm receipt of an order, either through the self-service Receive Orders web page, or by responding directly to the workflow notification, Oracle Web Employees automatically processes your receipt in Oracle Purchasing through the Receiving Transaction Processor.
The receiving transaction processor validates receipt transactions and updates the receiving history and purchase order tables in Oracle Purchasing. If the system encounters an error while processing the receiving transaction, notifications are sent to the requester and the buyer indicating the likely cause for failure.
Exception Handling
Oracle Web Employees handles the following exceptions as part of the Confirm Receipts workflow process:
Time Out Once a notification is sent, the process will be in a wait state to get a response from the user. If there is no response from the user, the system will treat it as a timeout and resend the notification after one week as a first reminder. If there is no response for another week, the notification is resent as a second reminder. If the user does not respond to the second reminder within twenty-four hours, a notification is sent to the user's manager.
Receiving Controls The receipt confirmation process enforces the receiving controls supported in Oracle Purchasing. For example, the system rejects received quantities that are greater than the tolerance level.
Configure the Receive Orders Web Page
Oracle Web Employees allows you to use the Web Applications Dictionary technology (a.k.a. Object Navigator) to tailor the layout and content of the Receive Orders self service web page. Use the Web Applications Manager to select the fields to display, the field, prompts, length, formatting characteristics, and include certain input fields as part of the receipt confirmation.
Setting Up Web Users
Each employee that will confirm receipts through Oracle Web Employees must be defined as a web user. To setup the Receive Orders Web Page for an employee you must:
Define a web user for employees who will receive orders through the web
Assign the Receive Orders functions to that user's list of responsibilities/functions
Respond to Receipt Confirmation Notifications
To respond to workflow generated receipt confirmation requests:
Login to Oracle Web Employees and select the "View Notifications" function.
Select a response to the receipt confirmation request:
Fully Received.
Over/Partially Received.
Not Received.
For partially/over received orders, drill down from the notification into the Receive Orders web page. Record your receipt details.
Select the "Submit Response" button to complete the response and initiate the next workflow activity.
Wednesday, November 9, 2011
R12: How to enable Personalization Page option
R12: How to enable Personalization Page option
FND: Personalization Region Link Enabled
Personalize Self-Service Defn
FND: Personalization Region Link Enabled
Personalize Self-Service Defn
Thursday, November 3, 2011
Credit Card Setup
How to setup Credit Cards Process in Oracle, below note id's will give detailed explanation
Credit Card Payments, Refunds and Chargebacks: Overview and Setup for Oracle Receivables Release 12 [ID 1357967.1]
Oracle Payments Minimum/Dummy Setup For Credit Card/Purchase Card Funds Capture Processing [ID 553614.1]
Credit Card Payments, Refunds and Chargebacks: Overview and Setup for Oracle Receivables Release 12 [ID 1357967.1]
Oracle Payments Minimum/Dummy Setup For Credit Card/Purchase Card Funds Capture Processing [ID 553614.1]
Sunday, October 30, 2011
How to Cancel Cost Manager (CMCTCM ) and Relaunch It
Applies to:
Oracle Cost Management - Version: 11.5.9 to 12.1.3 - Release: 11.5 to 12.1
Information in this document applies to any platform.
CMCTCM
Purpose
How to cancel cost manager, in order to avoid :
- to launch severals cost manager at the same time
- to have warning message in CMCTCM log saying :
"
A Cost Manager is already launched.
Action: If you want to launch a new manager, cancel earlier manager first."
Troubleshooting Details
How to cancel cost manager, in order to avoid :
- to launch severals cost manager at the same time
- to have warning message in CMCTCM log saying :
"A Cost Manager is already launched.
Action: If you want to launch a new manager, cancel earlier manager first."
1 - To cancel the Cost Manager : use one of the following method:
a) use 'System Administrator' responsibility
Navigate to Concurrent/Request/view
Set : specific Request=on
Name=Cost Manager.
In the lower part of the Find requests window there is a field "Select the Number of Days to view" set by default to 7 days.
-> Set "Select the Number of Days to view"=9999
In fact this field is a filter which limits how far back it will query requests.
From here Cancel the Cost Manager which is Pending Scheduled.
or
b) Use the following SQL script:
SELECT request_id RequestId,
request_date RequestDt, concurrent_program_name,
phase_code Phase,
status_code Status FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp
WHERE fcp.application_id = 702 AND
fcp.concurrent_program_name in ('CMCTCM', 'CMCMCW', 'CMCACW') AND
fcr.concurrent_program_id = fcp.concurrent_program_id AND
fcr.program_application_id = 702 AND fcr.phase_code <> 'C'
Cancel all the requests from the above output by navigating to, System Administrator> Concurrent> Request. Query for the RequestId returned from the select above.
In the lower part of the Find requests window there is a field "Select the Number of Days to view" set by default to 7 days
-> Set "Select the Number of Days to view"=9999
In fact this field is a filter which limits how far back it will query requests.
Cancel the request id other than running. Let the running request get over.
2 - Run the cmclean.sql script available from MOS Note 134007.1
3 - Restart the Cost Manager
Inventory> Setup : Transactions> Interface Managers
Select 'Cost Manager' - choose Tools > Launch Manager
Also review << Note 304313.1>>
NOTE:134007.1 - Concurrent Processing - CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables
NOTE:304313.1 - Understanding Cost Manager
Oracle Cost Management - Version: 11.5.9 to 12.1.3 - Release: 11.5 to 12.1
Information in this document applies to any platform.
CMCTCM
Purpose
How to cancel cost manager, in order to avoid :
- to launch severals cost manager at the same time
- to have warning message in CMCTCM log saying :
"
A Cost Manager is already launched.
Action: If you want to launch a new manager, cancel earlier manager first."
Troubleshooting Details
How to cancel cost manager, in order to avoid :
- to launch severals cost manager at the same time
- to have warning message in CMCTCM log saying :
"A Cost Manager is already launched.
Action: If you want to launch a new manager, cancel earlier manager first."
1 - To cancel the Cost Manager : use one of the following method:
a) use 'System Administrator' responsibility
Navigate to Concurrent/Request/view
Set : specific Request=on
Name=Cost Manager.
In the lower part of the Find requests window there is a field "Select the Number of Days to view" set by default to 7 days.
-> Set "Select the Number of Days to view"=9999
In fact this field is a filter which limits how far back it will query requests.
From here Cancel the Cost Manager which is Pending Scheduled.
or
b) Use the following SQL script:
SELECT request_id RequestId,
request_date RequestDt, concurrent_program_name,
phase_code Phase,
status_code Status FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp
WHERE fcp.application_id = 702 AND
fcp.concurrent_program_name in ('CMCTCM', 'CMCMCW', 'CMCACW') AND
fcr.concurrent_program_id = fcp.concurrent_program_id AND
fcr.program_application_id = 702 AND fcr.phase_code <> 'C'
Cancel all the requests from the above output by navigating to, System Administrator> Concurrent> Request. Query for the RequestId returned from the select above.
In the lower part of the Find requests window there is a field "Select the Number of Days to view" set by default to 7 days
-> Set "Select the Number of Days to view"=9999
In fact this field is a filter which limits how far back it will query requests.
Cancel the request id other than running. Let the running request get over.
2 - Run the cmclean.sql script available from MOS Note 134007.1
3 - Restart the Cost Manager
Inventory> Setup : Transactions> Interface Managers
Select 'Cost Manager' - choose Tools > Launch Manager
Also review << Note 304313.1>>
NOTE:134007.1 - Concurrent Processing - CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables
NOTE:304313.1 - Understanding Cost Manager
Friday, September 30, 2011
R12 Ap Invoice Data Fix for Tax Amount
Please execute the following data-fix to make Tax Amount Zero on the AP Invoice in R12
UPDATE ZX_LINES
SET UNROUNDED_TAX_AMT = 0,
TAX_AMT = 0,
TAX_AMT_TAX_CURR = 0,
TAX_AMT_FUNCL_CURR = 0,
CAL_TAX_AMT = 0,
CAL_TAX_AMT_TAX_CURR = 0,
CAL_TAX_AMT_FUNCL_CURR = 0,
CANCEL_FLAG = 'Y'
WHERE APPLICATION_ID = 200
AND ENTITY_CODE = 'AP_INVOICES'
AND EVENT_CLASS_CODE = 'STANDARD INVOICES'
AND TRX_ID = 90484;
COMMIT;
UPDATE ZX_LINES_SUMMARY
SET TAX_AMT = 0,
TAX_AMT_TAX_CURR = 0,
TAX_AMT_FUNCL_CURR = 0,
TOTAL_REC_TAX_AMT = 0,
TOTAL_REC_TAX_AMT_FUNCL_CURR = 0,
CANCEL_FLAG = 'Y'
WHERE APPLICATION_ID = 200
AND ENTITY_CODE = 'AP_INVOICES'
AND EVENT_CLASS_CODE = 'STANDARD INVOICES'
AND TRX_ID = 90484;
UPDATE AP_INVOICE_LINES_ALL
SET AMOUNT = 0,
BASE_AMOUNT = 0
WHERE INVOICE_ID = 90484
AND LINE_TYPE_LOOKUP_CODE = 'TAX';
COMMIT;
UPDATE AP_INVOICES_ALL
SET TOTAL_TAX_AMOUNT = 0
WHERE INVOICE_ID = 90484;
COMMIT;
UPDATE ZX_LINES
SET UNROUNDED_TAX_AMT = 0,
TAX_AMT = 0,
TAX_AMT_TAX_CURR = 0,
TAX_AMT_FUNCL_CURR = 0,
CAL_TAX_AMT = 0,
CAL_TAX_AMT_TAX_CURR = 0,
CAL_TAX_AMT_FUNCL_CURR = 0,
CANCEL_FLAG = 'Y'
WHERE APPLICATION_ID = 200
AND ENTITY_CODE = 'AP_INVOICES'
AND EVENT_CLASS_CODE = 'STANDARD INVOICES'
AND TRX_ID = 90484;
COMMIT;
UPDATE ZX_LINES_SUMMARY
SET TAX_AMT = 0,
TAX_AMT_TAX_CURR = 0,
TAX_AMT_FUNCL_CURR = 0,
TOTAL_REC_TAX_AMT = 0,
TOTAL_REC_TAX_AMT_FUNCL_CURR = 0,
CANCEL_FLAG = 'Y'
WHERE APPLICATION_ID = 200
AND ENTITY_CODE = 'AP_INVOICES'
AND EVENT_CLASS_CODE = 'STANDARD INVOICES'
AND TRX_ID = 90484;
UPDATE AP_INVOICE_LINES_ALL
SET AMOUNT = 0,
BASE_AMOUNT = 0
WHERE INVOICE_ID = 90484
AND LINE_TYPE_LOOKUP_CODE = 'TAX';
COMMIT;
UPDATE AP_INVOICES_ALL
SET TOTAL_TAX_AMOUNT = 0
WHERE INVOICE_ID = 90484;
COMMIT;
Wednesday, July 27, 2011
How to resubmit unprocessed records stuck in the MTL_TRANSACTIONS_INTERFACE table
How to resubmit unprocessed records stuck in the MTL_TRANSACTIONS_INTERFACE table
Solution
Record may be resubmitted through the Application or using SQL*Plus.
Note: Back up the data in the table prior to making any changes using SQL*Plus.
1.Resubmission through the Application
Navigation Inventory:Transactions:Transaction Open Interface
Click in the check box under the "Submit" column
then Save
2. Resubmission through SQL*Plus.
Update MTL_TRANSACTIONS_INTERFACE Set PROCESS_FLAG = 1,
LOCK_FLAG = 2, TRANSACTION_MODE = 3,
VALIDATION_REQUIRED = 1, ERROR_CODE = NULL,
ERROR_EXPLANATION = NULL Where PROCESS_FLAG IN (1,3);
Solution
Record may be resubmitted through the Application or using SQL*Plus.
Note: Back up the data in the table prior to making any changes using SQL*Plus.
1.Resubmission through the Application
Navigation Inventory:Transactions:Transaction Open Interface
Click in the check box under the "Submit" column
then Save
2. Resubmission through SQL*Plus.
Update MTL_TRANSACTIONS_INTERFACE Set PROCESS_FLAG = 1,
LOCK_FLAG = 2, TRANSACTION_MODE = 3,
VALIDATION_REQUIRED = 1, ERROR_CODE = NULL,
ERROR_EXPLANATION = NULL Where PROCESS_FLAG IN (1,3);
Records Stuck In Table Mtl_Transactions_Interface , Negative Balances Not Allowed
Run the following script, which will find items that do not have sufficient quantity in the STAGING subinventory.
Note: if you have given some name other than 'STAGE' to your staging subinventory, you will need to modify the lines in bold type
SELECT wdd.organization_id, wdd.inventory_item_id, msi.segment1,
SUM (wdd.requested_quantity) requested_qty,
SUM (NVL (wdd.cancelled_quantity, 0)) cancelled_qty,
SUM (mtrl.quantity) move_order_qty, MAX (oq.onhand_qty) onhand_qty,
SUM (wdd.requested_quantity) - MAX (oq.onhand_qty) change_qty
FROM wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_system_items msi,
(SELECT NVL (SUM (moq.transaction_quantity), 0) onhand_qty,
moq.organization_id, moq.inventory_item_id,
moq.subinventory_code
FROM apps.mtl_onhand_quantities moq
WHERE moq.containerized_flag = 2
AND moq.subinventory_code = 'STAGE'
GROUP BY moq.organization_id,
moq.inventory_item_id,
moq.subinventory_code) oq
WHERE wdd.released_status IN ('C', 'Y')
AND NVL (wdd.inv_interfaced_flag, 'N') IN ('N', 'P')
AND wdd.move_order_line_id = mtrl.line_id
AND wdd.inventory_item_id = oq.inventory_item_id
AND wdd.organization_id = oq.organization_id
AND msi.organization_id = wdd.organization_id
AND msi.inventory_item_id = wdd.inventory_item_id
AND oq.subinventory_code = 'STAGE'
GROUP BY wdd.organization_id, wdd.inventory_item_id, msi.segment1
HAVING SUM (wdd.requested_quantity) > MAX (oq.onhand_qty)
UNION ALL
SELECT wdd.organization_id, wdd.inventory_item_id, msi.segment1,
SUM (wdd.requested_quantity) requested_qty,
SUM (NVL (wdd.cancelled_quantity, 0)) cancelled_qty,
SUM (mtrl.quantity) move_order_qty, 0,
SUM (wdd.requested_quantity) change_qty
FROM wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_system_items msi
WHERE wdd.released_status IN ('Y', 'C')
AND NVL (wdd.inv_interfaced_flag, 'N') IN ('N', 'P')
AND wdd.move_order_line_id = mtrl.line_id
AND msi.organization_id = wdd.organization_id
AND msi.inventory_item_id = wdd.inventory_item_id
AND NOT EXISTS (
SELECT 1
FROM mtl_onhand_quantities oq
WHERE oq.inventory_item_id = wdd.inventory_item_id
AND oq.organization_id = wdd.organization_id
AND oq.subinventory_code = 'STAGE')
GROUP BY wdd.organization_id, wdd.inventory_item_id, msi.segment1
ORDER BY 1, 2
2. Perform subinventory transfers as needed to transfer the quantity identified by the column "change_qty" into the staging subinventory
Note: if you have given some name other than 'STAGE' to your staging subinventory, you will need to modify the lines in bold type
SELECT wdd.organization_id, wdd.inventory_item_id, msi.segment1,
SUM (wdd.requested_quantity) requested_qty,
SUM (NVL (wdd.cancelled_quantity, 0)) cancelled_qty,
SUM (mtrl.quantity) move_order_qty, MAX (oq.onhand_qty) onhand_qty,
SUM (wdd.requested_quantity) - MAX (oq.onhand_qty) change_qty
FROM wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_system_items msi,
(SELECT NVL (SUM (moq.transaction_quantity), 0) onhand_qty,
moq.organization_id, moq.inventory_item_id,
moq.subinventory_code
FROM apps.mtl_onhand_quantities moq
WHERE moq.containerized_flag = 2
AND moq.subinventory_code = 'STAGE'
GROUP BY moq.organization_id,
moq.inventory_item_id,
moq.subinventory_code) oq
WHERE wdd.released_status IN ('C', 'Y')
AND NVL (wdd.inv_interfaced_flag, 'N') IN ('N', 'P')
AND wdd.move_order_line_id = mtrl.line_id
AND wdd.inventory_item_id = oq.inventory_item_id
AND wdd.organization_id = oq.organization_id
AND msi.organization_id = wdd.organization_id
AND msi.inventory_item_id = wdd.inventory_item_id
AND oq.subinventory_code = 'STAGE'
GROUP BY wdd.organization_id, wdd.inventory_item_id, msi.segment1
HAVING SUM (wdd.requested_quantity) > MAX (oq.onhand_qty)
UNION ALL
SELECT wdd.organization_id, wdd.inventory_item_id, msi.segment1,
SUM (wdd.requested_quantity) requested_qty,
SUM (NVL (wdd.cancelled_quantity, 0)) cancelled_qty,
SUM (mtrl.quantity) move_order_qty, 0,
SUM (wdd.requested_quantity) change_qty
FROM wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_system_items msi
WHERE wdd.released_status IN ('Y', 'C')
AND NVL (wdd.inv_interfaced_flag, 'N') IN ('N', 'P')
AND wdd.move_order_line_id = mtrl.line_id
AND msi.organization_id = wdd.organization_id
AND msi.inventory_item_id = wdd.inventory_item_id
AND NOT EXISTS (
SELECT 1
FROM mtl_onhand_quantities oq
WHERE oq.inventory_item_id = wdd.inventory_item_id
AND oq.organization_id = wdd.organization_id
AND oq.subinventory_code = 'STAGE')
GROUP BY wdd.organization_id, wdd.inventory_item_id, msi.segment1
ORDER BY 1, 2
2. Perform subinventory transfers as needed to transfer the quantity identified by the column "change_qty" into the staging subinventory
Tuesday, July 26, 2011
R12: Change Application user password and end date from sqlplus
R12: Change Application user password and end date from sqlplus
DECLARE
v_user_name VARCHAR2(80) :'XXABC';
v_new_password VARCHAR2(80) := 'oracle123';
v_hard_password VARCHAR2(1) := fnd_profile.VALUE('SIGNON_PASSWORD_HARD_TO_GUESS');
BEGIN
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
,val => 'N');
-- Call the procedure
fnd_user_pkg.updateuser(x_user_name => v_user_name
,x_owner => 'CUST'
,x_unencrypted_password => v_new_password
,x_end_date => SYSDATE + 10000);
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
,val => v_hard_password);
COMMIT ;
EXCEPTION
WHEN OTHERS THEN
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
,val => v_hard_password);
RAISE;
END;
DECLARE
v_user_name VARCHAR2(80) :'XXABC';
v_new_password VARCHAR2(80) := 'oracle123';
v_hard_password VARCHAR2(1) := fnd_profile.VALUE('SIGNON_PASSWORD_HARD_TO_GUESS');
BEGIN
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
,val => 'N');
-- Call the procedure
fnd_user_pkg.updateuser(x_user_name => v_user_name
,x_owner => 'CUST'
,x_unencrypted_password => v_new_password
,x_end_date => SYSDATE + 10000);
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
,val => v_hard_password);
COMMIT ;
EXCEPTION
WHEN OTHERS THEN
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS'
,val => v_hard_password);
RAISE;
END;
Wednesday, June 29, 2011
OM Useful Tips
Allocation Error During Transact Move Order
"Error: The material sourcing process failed to create picking suggestions for line 2 of move order number 59641464"
"APP-INV-05297: Invalid transaction and serial control combination"
Encountered the above error message after clicked on "Allocate" button in Transact Move Orders form. Even after I backordered the line, I could not proceed to Pick Release, failed Pick Release.
Root Cause: Item lot reserved for the order line is no more valid.
Action: Unreserve the invalid item lot, pick release, transact move order & ship confirm successfully.
Cannot Perform Shipping Transactions At Shipping Transactions form, Action combo box is disabled. Can't perform pick release, create delivery, etc.
Grant role to user at OM responsibility. At Shipping Execution Grants form, add record, enter user name & save.
Setup -> Shipping -> Grants and Role Definitions -> Grants
Default role is "Upgrade Role", which you can maintain it in
Setup -> Shipping -> Grants and Role Definitions -> Define Roles
Launch Pick Release Failed My Sales Order (SO) has been booked successfully. Order header status is Booked, order line status is Awaiting Shipping. Quantity on-hand is sufficient. At Shipping Transactions form, I performed "Launch Pick Release", order line status remained Awaiting Shipping, it did not change to Picked.
No warning or error was prompted.
In my case, it was due to Credit Hold. Go Order Header -> Actions -> Additional Order Information, I saw Hold with message "Credit check hold applied. Overall limit exceeded.". GoActions -> Release Holds to release hold before proceed to Pick Release.
In order to perform Release Holds action, you need to be given authorities. Get the Holds Name and check the authorizations at Setup -> Orders -> Holds.
Where to Setup New Sales Order Type Under Order Management responsibility, Setup -> Transactions Types -> Define
Pick List Ship-To Address Not Get Updated
It is an address issue.
The Ship-to Address appears on the Pick List (for ISO) is retrieved from wsh_locations table. As for IR-ISO setup, ship-to-address comes from Customer address instead of Location address.
When we updates the customer address, hz_locations table get update. By right, bothhz_locations table and wsh_locations table should be synchronized automatically. But, in my case, it was not. Business event "oracle.apps.ar.hz.Location.update" is enabled, everything else is fine. But the synchronize is not happening.
Logged a tar and Oracle came back with suggestion to run "Import Shipping Location" program in OM responsibility to synchronize the table. These was their reply (copied from some another article).
"...The 'Import Shipping locations' concurrent program imports all of the internal and external
locations from HR/HZ LOCATIONS tables into the WSH_LOCATIONS tables.
This program should be executed at least once when upgrading to 11.5.9. After the
upgrade, it should not be required to rerun this concurrent program since any
changes in the HR/HZ LOCATIONS tables are automatically synced up with WSH
tables.
There are a few exceptions when Import Shipping Locations concurrent program should be run. These exceptions would be only when any updates to a
Customer's information does not get reflected when creating a new sales order
or when you find any inconsistencies in Location records when interfaced from
HR to WSH Locations, such as an incorrect country code..."
Question is the auto-sync is not happening and Oracle can't give reason why.
End up I have to run "Import Shipping Location" program to solve the issue (and it took 3.5 hours to complete with the Start-Date value = sysdate-4).
HL Pick Slip SRS Report -> Completed Warning After Pick Release action, pick slip report will get submitted, but it often completed warning.
How to know if actual picking taken place? Picking could be failed or success even the program completed warning. E.g. For printing issue, even it completed warning, but the stock has been picked.
From Requests list, click on the request, view Output. If there is records there, it was successfully picked. Another faster way, observe the request in the list, if the 4th and 5th parameters have value, it is successful.
Unable to Pick Release Internal Sales Order (ISO)
ISO was created from Internal Requisition (IR), order line status is "Awaiting Shipping", shipment line status is "Ready to Release", next step is "Pick Release". Performed Pick Release, shipment line status is not changed. As usual, no error shown in the log.
After investigation, found onhand no issue, inventory period is opened, what else could be the cause?
Ans: The ISO has tied to subinventory ABC, but the manual Pick Release is picked from subinventory DEF. So, change the "Pick From" subinventory to ABC, problem solved. Note: ISO subinventory is defaulted from IR subinventory.
Meaning of wsh_new_deliveries.released_status
Meaning of wsh_new_deliveries.released_status field value, reproduced from Document406189.1.
B Backordered Line failed to be allocated in Inventory
C Shipped Line has been shipped
D Cancelled Line is Cancelled
N Not ready for release Line is not ready to be released
R Ready to release Line is ready to be released
S Released to Warehouse Line has been released to Inventory for processing
X Not Applicable Line is not applicable for Pick Release
Y Staged Line has been picked and staged by Inventory
Order Shipping Line Status Stuck in "Shipped", Next Step "Run Interfaces" Obviously "Interface Trip Stop" program has been ran, but status is not change. What could be the reason behind?
Check error records in Transaction Open Interface (Transactions->Transaction Open Interface). Any error stuck with error in the interface will cause this issue.
Table:
mtl_transactions_interface
Fields:
process_flag => 3, if error
organization_id => Inventory Organization
error_code
OE_ORDER_HEADERS_ALL -> SOLD_TO_ORG_ID and SHIP_TO_ORG_ID At the Order Header table, SOLD_TO_ORG_ID refers to customer_id whereas SHIP_TO_ORG_ID is the ORGANIZATION_ID from OE_SHIP_TO_ORGS_V. From the SHIP_TO_ORG_ID field, we can trace back the actual ship-to address for a particular order.
Before we can use OE_SHIP_TO_ORGS_V view in sql, we have to first initialize the view. However, we can skip it by going direct to the tables.
--to get actual ship to address for a Sales Order
select hl.*
from
apps.hz_cust_site_uses_all cs,
apps.hz_cust_acct_sites_all cas,
apps.hz_party_sites ps,
apps.hz_locations hl
where
hl.location_id=ps.location_id and
ps.party_site_id=cas.party_site_id and
cas.cust_acct_site_id=cs.cust_acct_site_id and
hl.country='IN' and
hl.state='JAMMU AND KASHMIR' and
cs.site_use_code='SHIP_TO' and
cs.org_id=123
order by cs.site_use_id desc
--to get order base on ship to address
-- cs.site_use_id equals oe_order_headers_all.ship_to_org_id
select * from apps.oe_order_headers_all where ship_to_org_id in (
select cs.site_use_id
from
apps.hz_cust_site_uses_all cs,
apps.hz_cust_acct_sites_all cas,
apps.hz_party_sites ps,
apps.hz_locations hl
where
hl.location_id=ps.location_id and
ps.party_site_id=cas.party_site_id and
cas.cust_acct_site_id=cs.cust_acct_site_id and
hl.country='IN' and
hl.state='JAMMU AND KASHMIR' and
cs.site_use_code='SHIP_TO' and
cs.org_id=123 )
and order_type_id=2345
Validation failed for the field - Ship To
Encountered this error during Order Copy (at header level).
Root Cause:
Customer Site Location is deactivated.
Fix:
Activate it. Customer->Standard. Choose the correct address base on the ship to address shown in the sale order, click "Open". Identify "Ship To" underUsage column, check the Active checkbox.
Some useful backend table for these:
Take oe_order_headers_all.ship_to_org_id to hz_cust_site_uses_all.site_use_id, hz_cust_site_uses_all.location field refers Customer Site Location.
We can get the Customer Site Number by using
select ps.party_site_number
from
apps.hz_cust_site_uses_all cs,
apps.hz_cust_acct_sites_all cas,
apps.hz_party_sites ps,
apps.hz_locations hl
where
hl.location_id=ps.location_id and
ps.party_site_id=cas.party_site_id and
cas.cust_acct_site_id=cs.cust_acct_site_id and
cs.site_use_code='SHIP_TO' and
cs.org_id=123 and
cs.site_use_id = 12345678
Query Manager for Shipping Transactions Form
Org Code is not defaulted to the OU's inventory organization, but it is defaulted to another inventory organization which has no relationship with current OU. Somemore, Org Code field is disabled. Why?
Ans: Setup->Shipping->Grants and Role Definitions->Grants. Grant to your Oracle user id, the Org Code will be enabled with blank. Set the Org Code from there (the field is optional).
Shipping Method - Lookups
To look at the complete list of Shipping Method, you need to go to Setup->Organizations->Shipping Methods in Inventory responsibility. It is a System Lookups, type: SHIP_METHOD.You can add/modify/remove Shipping Method there.
Picked Quantity Auto Transferred Back After Backordered?
Picked quantity (Pick Release process) will NOT be transferred back automatically to original locator/subinventory after Backordered transaction.
1 Delivery Has Many Delivery Lines, How to Backorder Only 1 Delivery Line
At Shipping Transactions form, query the delivery line by order number and item code (assuming this item has only 1 delivery line), enter the Shipped Qty as 0, Backordered Qty as the total quantity at the delivery line.Click Delivery tab, select "Ship Entered Quantities", press OK. If a delivery has multiple lines, only the selected line will be backordered.
Setting Up Internal Customer for IR-ISO
How does customer get recognized during Internal Sales Order (ISO) creation?
Ans: Location. In IR, there is a Ship-to-Location, and this internal location must tie to the same location defined in Customer Address (Customers->Addresses->Location->Internal Location). Each unique location can only assigned to only 1 customer and 1 address.Table for customer address and location: RA_SITE_USES_ALL
Auto Allocation in Transact Move Order for a Sales Order If the SO shipment status is now "Released to Warehouse", we need to perform Transact Move Orders. For the Move Order, which setup trigger auto allocation?
Ans: In the Release Rules form, Inventory tab, Auto Allocate selection (Yes/No)
No Delivery Detail Found After Ship-Confirm action, shipping line status is Closed, Interface Trip Stop program completed Warning, with warning message "No Delivery Detail Found" in the log file.
Where to rectify this?
Ans: Go Inventory responsibility, Transactions->Transaction Open Interface, check for any errors in the shipping organization. Rectify from there.
Pick Release in Shipping Transactions Form This is an alternate way to perform pick release at a place other than Release Sales Ordersform.
Shipping->Transactions
Only Lines/LPNs tab is enabled, other tabs like "Delivery", "Path by Stop" & "Path by Trip" are disabled as picking is not done yet. Go "Action" LOV, choose "Launch Pick Release", clickGo button.
Internal Requisition Approved, Internal Sales Order Created and Picked, Need to Cancel All, How? Ans: First perform backorder for ISO, cancel the ISO, then cancel the IR. Do remember to move back the inventory from staging to picking.
Collection from unknown author ( oraclea2z )
"Error: The material sourcing process failed to create picking suggestions for line 2 of move order number 59641464"
"APP-INV-05297: Invalid transaction and serial control combination"
Encountered the above error message after clicked on "Allocate" button in Transact Move Orders form. Even after I backordered the line, I could not proceed to Pick Release, failed Pick Release.
Root Cause: Item lot reserved for the order line is no more valid.
Action: Unreserve the invalid item lot, pick release, transact move order & ship confirm successfully.
Cannot Perform Shipping Transactions At Shipping Transactions form, Action combo box is disabled. Can't perform pick release, create delivery, etc.
Grant role to user at OM responsibility. At Shipping Execution Grants form, add record, enter user name & save.
Setup -> Shipping -> Grants and Role Definitions -> Grants
Default role is "Upgrade Role", which you can maintain it in
Setup -> Shipping -> Grants and Role Definitions -> Define Roles
Launch Pick Release Failed My Sales Order (SO) has been booked successfully. Order header status is Booked, order line status is Awaiting Shipping. Quantity on-hand is sufficient. At Shipping Transactions form, I performed "Launch Pick Release", order line status remained Awaiting Shipping, it did not change to Picked.
No warning or error was prompted.
In my case, it was due to Credit Hold. Go Order Header -> Actions -> Additional Order Information, I saw Hold with message "Credit check hold applied. Overall limit exceeded.". GoActions -> Release Holds to release hold before proceed to Pick Release.
In order to perform Release Holds action, you need to be given authorities. Get the Holds Name and check the authorizations at Setup -> Orders -> Holds.
Where to Setup New Sales Order Type Under Order Management responsibility, Setup -> Transactions Types -> Define
Pick List Ship-To Address Not Get Updated
It is an address issue.
The Ship-to Address appears on the Pick List (for ISO) is retrieved from wsh_locations table. As for IR-ISO setup, ship-to-address comes from Customer address instead of Location address.
When we updates the customer address, hz_locations table get update. By right, bothhz_locations table and wsh_locations table should be synchronized automatically. But, in my case, it was not. Business event "oracle.apps.ar.hz.Location.update" is enabled, everything else is fine. But the synchronize is not happening.
Logged a tar and Oracle came back with suggestion to run "Import Shipping Location" program in OM responsibility to synchronize the table. These was their reply (copied from some another article).
"...The 'Import Shipping locations' concurrent program imports all of the internal and external
locations from HR/HZ LOCATIONS tables into the WSH_LOCATIONS tables.
This program should be executed at least once when upgrading to 11.5.9. After the
upgrade, it should not be required to rerun this concurrent program since any
changes in the HR/HZ LOCATIONS tables are automatically synced up with WSH
tables.
There are a few exceptions when Import Shipping Locations concurrent program should be run. These exceptions would be only when any updates to a
Customer's information does not get reflected when creating a new sales order
or when you find any inconsistencies in Location records when interfaced from
HR to WSH Locations, such as an incorrect country code..."
Question is the auto-sync is not happening and Oracle can't give reason why.
End up I have to run "Import Shipping Location" program to solve the issue (and it took 3.5 hours to complete with the Start-Date value = sysdate-4).
HL Pick Slip SRS Report -> Completed Warning After Pick Release action, pick slip report will get submitted, but it often completed warning.
How to know if actual picking taken place? Picking could be failed or success even the program completed warning. E.g. For printing issue, even it completed warning, but the stock has been picked.
From Requests list, click on the request, view Output. If there is records there, it was successfully picked. Another faster way, observe the request in the list, if the 4th and 5th parameters have value, it is successful.
Unable to Pick Release Internal Sales Order (ISO)
ISO was created from Internal Requisition (IR), order line status is "Awaiting Shipping", shipment line status is "Ready to Release", next step is "Pick Release". Performed Pick Release, shipment line status is not changed. As usual, no error shown in the log.
After investigation, found onhand no issue, inventory period is opened, what else could be the cause?
Ans: The ISO has tied to subinventory ABC, but the manual Pick Release is picked from subinventory DEF. So, change the "Pick From" subinventory to ABC, problem solved. Note: ISO subinventory is defaulted from IR subinventory.
Meaning of wsh_new_deliveries.released_status
Meaning of wsh_new_deliveries.released_status field value, reproduced from Document406189.1.
B Backordered Line failed to be allocated in Inventory
C Shipped Line has been shipped
D Cancelled Line is Cancelled
N Not ready for release Line is not ready to be released
R Ready to release Line is ready to be released
S Released to Warehouse Line has been released to Inventory for processing
X Not Applicable Line is not applicable for Pick Release
Y Staged Line has been picked and staged by Inventory
Order Shipping Line Status Stuck in "Shipped", Next Step "Run Interfaces" Obviously "Interface Trip Stop" program has been ran, but status is not change. What could be the reason behind?
Check error records in Transaction Open Interface (Transactions->Transaction Open Interface). Any error stuck with error in the interface will cause this issue.
Table:
mtl_transactions_interface
Fields:
process_flag => 3, if error
organization_id => Inventory Organization
error_code
OE_ORDER_HEADERS_ALL -> SOLD_TO_ORG_ID and SHIP_TO_ORG_ID At the Order Header table, SOLD_TO_ORG_ID refers to customer_id whereas SHIP_TO_ORG_ID is the ORGANIZATION_ID from OE_SHIP_TO_ORGS_V. From the SHIP_TO_ORG_ID field, we can trace back the actual ship-to address for a particular order.
Before we can use OE_SHIP_TO_ORGS_V view in sql, we have to first initialize the view. However, we can skip it by going direct to the tables.
--to get actual ship to address for a Sales Order
select hl.*
from
apps.hz_cust_site_uses_all cs,
apps.hz_cust_acct_sites_all cas,
apps.hz_party_sites ps,
apps.hz_locations hl
where
hl.location_id=ps.location_id and
ps.party_site_id=cas.party_site_id and
cas.cust_acct_site_id=cs.cust_acct_site_id and
hl.country='IN' and
hl.state='JAMMU AND KASHMIR' and
cs.site_use_code='SHIP_TO' and
cs.org_id=123
order by cs.site_use_id desc
--to get order base on ship to address
-- cs.site_use_id equals oe_order_headers_all.ship_to_org_id
select * from apps.oe_order_headers_all where ship_to_org_id in (
select cs.site_use_id
from
apps.hz_cust_site_uses_all cs,
apps.hz_cust_acct_sites_all cas,
apps.hz_party_sites ps,
apps.hz_locations hl
where
hl.location_id=ps.location_id and
ps.party_site_id=cas.party_site_id and
cas.cust_acct_site_id=cs.cust_acct_site_id and
hl.country='IN' and
hl.state='JAMMU AND KASHMIR' and
cs.site_use_code='SHIP_TO' and
cs.org_id=123 )
and order_type_id=2345
Validation failed for the field - Ship To
Encountered this error during Order Copy (at header level).
Root Cause:
Customer Site Location is deactivated.
Fix:
Activate it. Customer->Standard. Choose the correct address base on the ship to address shown in the sale order, click "Open". Identify "Ship To" underUsage column, check the Active checkbox.
Some useful backend table for these:
Take oe_order_headers_all.ship_to_org_id to hz_cust_site_uses_all.site_use_id, hz_cust_site_uses_all.location field refers Customer Site Location.
We can get the Customer Site Number by using
select ps.party_site_number
from
apps.hz_cust_site_uses_all cs,
apps.hz_cust_acct_sites_all cas,
apps.hz_party_sites ps,
apps.hz_locations hl
where
hl.location_id=ps.location_id and
ps.party_site_id=cas.party_site_id and
cas.cust_acct_site_id=cs.cust_acct_site_id and
cs.site_use_code='SHIP_TO' and
cs.org_id=123 and
cs.site_use_id = 12345678
Query Manager for Shipping Transactions Form
Org Code is not defaulted to the OU's inventory organization, but it is defaulted to another inventory organization which has no relationship with current OU. Somemore, Org Code field is disabled. Why?
Ans: Setup->Shipping->Grants and Role Definitions->Grants. Grant to your Oracle user id, the Org Code will be enabled with blank. Set the Org Code from there (the field is optional).
Shipping Method - Lookups
To look at the complete list of Shipping Method, you need to go to Setup->Organizations->Shipping Methods in Inventory responsibility. It is a System Lookups, type: SHIP_METHOD.You can add/modify/remove Shipping Method there.
Picked Quantity Auto Transferred Back After Backordered?
Picked quantity (Pick Release process) will NOT be transferred back automatically to original locator/subinventory after Backordered transaction.
1 Delivery Has Many Delivery Lines, How to Backorder Only 1 Delivery Line
At Shipping Transactions form, query the delivery line by order number and item code (assuming this item has only 1 delivery line), enter the Shipped Qty as 0, Backordered Qty as the total quantity at the delivery line.Click Delivery tab, select "Ship Entered Quantities", press OK. If a delivery has multiple lines, only the selected line will be backordered.
Setting Up Internal Customer for IR-ISO
How does customer get recognized during Internal Sales Order (ISO) creation?
Ans: Location. In IR, there is a Ship-to-Location, and this internal location must tie to the same location defined in Customer Address (Customers->Addresses->Location->Internal Location). Each unique location can only assigned to only 1 customer and 1 address.Table for customer address and location: RA_SITE_USES_ALL
Auto Allocation in Transact Move Order for a Sales Order If the SO shipment status is now "Released to Warehouse", we need to perform Transact Move Orders. For the Move Order, which setup trigger auto allocation?
Ans: In the Release Rules form, Inventory tab, Auto Allocate selection (Yes/No)
No Delivery Detail Found After Ship-Confirm action, shipping line status is Closed, Interface Trip Stop program completed Warning, with warning message "No Delivery Detail Found" in the log file.
Where to rectify this?
Ans: Go Inventory responsibility, Transactions->Transaction Open Interface, check for any errors in the shipping organization. Rectify from there.
Pick Release in Shipping Transactions Form This is an alternate way to perform pick release at a place other than Release Sales Ordersform.
Shipping->Transactions
Only Lines/LPNs tab is enabled, other tabs like "Delivery", "Path by Stop" & "Path by Trip" are disabled as picking is not done yet. Go "Action" LOV, choose "Launch Pick Release", clickGo button.
Internal Requisition Approved, Internal Sales Order Created and Picked, Need to Cancel All, How? Ans: First perform backorder for ISO, cancel the ISO, then cancel the IR. Do remember to move back the inventory from staging to picking.
Collection from unknown author ( oraclea2z )
Friday, June 24, 2011
Concurrent Manager Queries
/* Managers with their corresponding Oracle and System Process Id''s*/
SELECT DISTINCT concurrent_process_id cpid, gvs.inst_id INSTANCE,
gvp.pid opid, os_process_id osid,
q.user_concurrent_queue_name manager, p.node_name node,
TO_CHAR (p.process_start_date,
'MM-DD-YY HH:MI:SSAM'
) started_at
FROM fnd_concurrent_processes p,
fnd_concurrent_queues_vl q,
gv$process gvp,
gv$session gvs
WHERE q.application_id = queue_application_id
AND (q.concurrent_queue_id = p.concurrent_queue_id)
AND (gvs.process = os_process_id)
AND (gvp.addr = gvs.paddr)
AND process_status_code NOT IN ('K', 'S')
ORDER BY gvs.inst_id,
p.node_name,
concurrent_process_id,
q.user_concurrent_queue_name;
/*Managers that are having problems*/
SELECT DISTINCT concurrent_process_id cpid, oracle_process_id opid,
os_process_id osid, user_concurrent_queue_name manager,
p.node_name node,
TO_CHAR (p.process_start_date,
'MM-DD-YY HH:MI:SSAM'
) started_at
FROM fnd_concurrent_processes p, fnd_concurrent_queues_vl q
WHERE q.application_id = queue_application_id
AND q.concurrent_queue_id = p.concurrent_queue_id
AND p.concurrent_queue_id <> 1
AND (os_process_id) NOT IN (SELECT gvs.process
FROM gv$session gvs
WHERE gvs.process IS NOT NULL)
AND ( process_status_code = 'A'
OR process_status_code = 'R'
OR process_status_code = 'T'
)
ORDER BY os_process_id,
concurrent_process_id,
q.user_concurrent_queue_name;
/*Managers that are not active at this instant*/
SELECT application_name, concurrent_queue_id qid,
user_concurrent_queue_name manager, node_name node
FROM fnd_concurrent_queues_vl q, fnd_application_vl a
WHERE a.application_id = q.application_id
AND max_processes = 0
AND running_processes = 0
AND q.concurrent_queue_id <> 1
ORDER BY application_name, user_concurrent_queue_name;
SELECT DISTINCT concurrent_process_id cpid, gvs.inst_id INSTANCE,
gvp.pid opid, os_process_id osid,
q.user_concurrent_queue_name manager, p.node_name node,
TO_CHAR (p.process_start_date,
'MM-DD-YY HH:MI:SSAM'
) started_at
FROM fnd_concurrent_processes p,
fnd_concurrent_queues_vl q,
gv$process gvp,
gv$session gvs
WHERE q.application_id = queue_application_id
AND (q.concurrent_queue_id = p.concurrent_queue_id)
AND (gvs.process = os_process_id)
AND (gvp.addr = gvs.paddr)
AND process_status_code NOT IN ('K', 'S')
ORDER BY gvs.inst_id,
p.node_name,
concurrent_process_id,
q.user_concurrent_queue_name;
/*Managers that are having problems*/
SELECT DISTINCT concurrent_process_id cpid, oracle_process_id opid,
os_process_id osid, user_concurrent_queue_name manager,
p.node_name node,
TO_CHAR (p.process_start_date,
'MM-DD-YY HH:MI:SSAM'
) started_at
FROM fnd_concurrent_processes p, fnd_concurrent_queues_vl q
WHERE q.application_id = queue_application_id
AND q.concurrent_queue_id = p.concurrent_queue_id
AND p.concurrent_queue_id <> 1
AND (os_process_id) NOT IN (SELECT gvs.process
FROM gv$session gvs
WHERE gvs.process IS NOT NULL)
AND ( process_status_code = 'A'
OR process_status_code = 'R'
OR process_status_code = 'T'
)
ORDER BY os_process_id,
concurrent_process_id,
q.user_concurrent_queue_name;
/*Managers that are not active at this instant*/
SELECT application_name, concurrent_queue_id qid,
user_concurrent_queue_name manager, node_name node
FROM fnd_concurrent_queues_vl q, fnd_application_vl a
WHERE a.application_id = q.application_id
AND max_processes = 0
AND running_processes = 0
AND q.concurrent_queue_id <> 1
ORDER BY application_name, user_concurrent_queue_name;
Saturday, May 28, 2011
Setup to perform OA Page personalization
To perform OA Page personalization a profile option (“Personaliz Self-Service Defn”) has to be assigned to a user login.
Navigation: “Application Administrator” Responsibility --> Application Administrator -->Profile
Here in this screen select ‘User’ Checkbox and then select your login user name and Select the Profile option “Personaliz Self-Service Defn” in the Profile text box. After selecting the profile option, click on Find button. This action opens System profile options screen.
In the screen select ‘Yes’ Option from the LOV corresponding to the user column.
Then save and close the form.
Navigation: “Application Administrator” Responsibility --> Application Administrator -->Profile
Here in this screen select ‘User’ Checkbox and then select your login user name and Select the Profile option “Personaliz Self-Service Defn” in the Profile text box. After selecting the profile option, click on Find button. This action opens System profile options screen.
In the screen select ‘Yes’ Option from the LOV corresponding to the user column.
Then save and close the form.
Wednesday, May 25, 2011
How to get table information? How to get About this Page link in OA page in R12
How to get table information? How to get About this Page link in OA page in R12
Applies to:
OA Pages Version: 12.0.0
Information in this document applies to any platform.
Goal
Q1: How to find supplier related view / table information in web page environment?
Q2: How to get the About this Page link in OA page?
Solution
A1: Please follow the following steps to get the Supplier related table information:
> Navigate to the Supplier Page
> Click on the About this page link
> Click Expand All
> You will be seeing SuppSrchVO, SupplierVO and SitesVO
> If you click on that view it will show you the query used.
A2: To get the About this Page Link:
Please ensure the below Profile option is set to Yes at site level:
FND: Diagnostics
System Administrator > Profile > System
The FND: Diagnostics profile option controls whether the Diagnostics button is rendered.
It also controls the display of the About this Page link.
You may also set below
Personalize Self-Service Defn : Yes
FND: Personalization Region Link Enabled : Yes
FND: Diagnostics : Yes
Add the below responsibilities to the user:
System Administrator > Security > User > Define
1. FND Html Forms
2. Functional Administrator
3. Functional Developer
4. Once done bounce the apache
5. Retest the issue
6. Migrate the solution to other environments as appropriate.
Applies to:
OA Pages Version: 12.0.0
Information in this document applies to any platform.
Goal
Q1: How to find supplier related view / table information in web page environment?
Q2: How to get the About this Page link in OA page?
Solution
A1: Please follow the following steps to get the Supplier related table information:
> Navigate to the Supplier Page
> Click on the About this page link
> Click Expand All
> You will be seeing SuppSrchVO, SupplierVO and SitesVO
> If you click on that view it will show you the query used.
A2: To get the About this Page Link:
Please ensure the below Profile option is set to Yes at site level:
FND: Diagnostics
System Administrator > Profile > System
The FND: Diagnostics profile option controls whether the Diagnostics button is rendered.
It also controls the display of the About this Page link.
You may also set below
Personalize Self-Service Defn : Yes
FND: Personalization Region Link Enabled : Yes
FND: Diagnostics : Yes
Add the below responsibilities to the user:
System Administrator > Security > User > Define
1. FND Html Forms
2. Functional Administrator
3. Functional Developer
4. Once done bounce the apache
5. Retest the issue
6. Migrate the solution to other environments as appropriate.
Tuesday, May 10, 2011
iRecruitment workflow
How to view Item Key and Workflow internal name for iRecruitment vacancy related workflows
SELECT transaction_document
FROM hr_api_transactions
WHERE transaction_ref_table = 'PER_ALL_VACANCIES'
and transaction_ref_id = 9876;
(drop the 'IRC' prefix when using transaction_ref_id).
The following query will return you item_type,item_key thorugh which you can search the workflow
SELECT item_type,item_key
FROM hr_api_transactions
WHERE transaction_ref_table = 'PER_ALL_VACANCIES'
and transaction_ref_id = 9204;
select * from PER_ALL_VACANCIES
where name ='IRC9204'
SELECT transaction_document
FROM hr_api_transactions
WHERE transaction_ref_table = 'PER_ALL_VACANCIES'
and transaction_ref_id = 9876;
(drop the 'IRC' prefix when using transaction_ref_id).
The following query will return you item_type,item_key thorugh which you can search the workflow
SELECT item_type,item_key
FROM hr_api_transactions
WHERE transaction_ref_table = 'PER_ALL_VACANCIES'
and transaction_ref_id = 9204;
select * from PER_ALL_VACANCIES
where name ='IRC9204'
How to clear the Cache in R12 JSP Pages
How to clear the Cache in R12 JSP Pages
Logon to oracle applications than navigate to
Functional Administrator-->Home--> Click on 'Core Services'--> click on 'Caching Framework'
Here you can clear Cache.
This is useful when you add responsibilities to your user or want to clear any cache
Logon to oracle applications than navigate to
Functional Administrator-->Home--> Click on 'Core Services'--> click on 'Caching Framework'
Here you can clear Cache.
This is useful when you add responsibilities to your user or want to clear any cache
Wednesday, March 23, 2011
Operating Unit and Inventory Organization link
Query which gives Operating Unit Information and corresponding Inventory Orgs related information also
SELECT hou.NAME operating_unit_name, hou.short_code,
hou.organization_id operating_unit_id, hou.set_of_books_id,
hou.business_group_id,
ood.organization_name inventory_organization_name,
ood.organization_code Inv_organization_code, ood.organization_id Inv_organization_id, ood.chart_of_accounts_id
FROM hr_operating_units hou, org_organization_definitions ood
WHERE 1 = 1 AND hou.organization_id = ood.operating_unit
ORDER BY hou.organization_id ASC
SELECT hou.NAME operating_unit_name, hou.short_code,
hou.organization_id operating_unit_id, hou.set_of_books_id,
hou.business_group_id,
ood.organization_name inventory_organization_name,
ood.organization_code Inv_organization_code, ood.organization_id Inv_organization_id, ood.chart_of_accounts_id
FROM hr_operating_units hou, org_organization_definitions ood
WHERE 1 = 1 AND hou.organization_id = ood.operating_unit
ORDER BY hou.organization_id ASC
Thursday, February 24, 2011
How to Delegate to enter the expense for some other person
Go to System Administrator>Security> Users
Query for the User’s record. Under Securing Attributes tab, add the below details:
Attribute: ICX_HR_PERSON_ID
Application: Self-Service Web Applications
Value: Employee Id or the Person_id to whom the access to enter the expense reports has to be given.
Query for the User’s record. Under Securing Attributes tab, add the below details:
Attribute: ICX_HR_PERSON_ID
Application: Self-Service Web Applications
Value: Employee Id or the Person_id to whom the access to enter the expense reports has to be given.
Tuesday, February 22, 2011
Tehnical Flow of OM
Understanding data flow for “Standard Order”
1. Order Entry
This is first stage when Order in enter in system.When the order is entered it basically create a record in order headers and Order Lines table.
•oe_order_headers_all (Here the flow_status_code as entered)
•oe_order_lines_all (flow_status_code as entered) ( order number is generated)
2.Order Booking
This is next stage , when Order which is entered in step 1 is booked and Flow status changed from Entered to Booked.At this stage , these table get affected.
•oe_order_headers_all (flow_status_code as booked ,booked_flag updated)
•oe_order_lines_all (flow_status_code as awaiting shipping, booked_flag updated)
•wsh_new_deliveries (status_code OP open)
•wsh_delivery_details (released_status ‘R’ ready to release)
Same time, Demand interface program runs in background And insert into inventory tables mtl_demand
3. Reservation
This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved.Once this program get successfully get completed , the mtl_reservations table get updated.
4. Pick Release
Ideally pick release is the process which is defined in which the items on the sales order are taken out from inventory.
Normally pick release SRS program runs in background . Once the program get completed these are the table get affected:
•oe_order_lines_all (flow_status_code ‘PICKED’ )
•wsh_delivery_details (released_status ‘S’ ‘submitted for release’ )
•mtl_txn_request_headers
•mtl_txn_request_lines
(move order tables.Here request is generated to move item from saleble to staging sub inventory)
•Mtl_material_transactions_temp (link to above tables through move_order_header_id/line_id
5.Pick Confirm
Items are transferred from saleble to staging Subinventory.
•mtl_material_transactions
•mtl_transaction_accounts
•wsh_delivery_details (released_status ‘Y’‘Released’ )
•wsh_delivery_assignments
6.Ship Confirm
Here ship confirm interface program runs in background . Data removed from wsh_new_deliveries
•oe_order_lines_all (flow_status_code ‘shipped’)
•wsh_delivery_details (released_status ‘C’ ‘Shipped’)
•mtl_transaction_interface
•mtl_material_transactions(linked through Transaction source header id)
•mtl_transaction_accounts
•Data deleted from mtl_demand,mtl_reservations
•Item deducted from mtl_onhand_quantities
7.Enter Invoice
This is also called Receivables interface, that mean information moved to accounting area for invoicing details.
•Invoicing workflow activity transfers shipped item information to Oracle Receivables.
•ra_interface_lines_all (interface table into which the data is transferred from order management)T
•Then Autoinvoice program imports data from this
•Table which get affected into this stage are recievables base table.
ra_customer_trx_all (cust_trx_id is primary key to link it to trx_lines table and trx_number is the invoice number)
ra_customer_trx_lines_all (line_attribute_1 and line_attribute_6 are linked to header_id (or order number) and line_id of the orders)
8.Complete Line
In this stage order line leval table get updated with Flow status and open flag.
•oe_order_lines_all (flow_status_code ‘shipped’, open_flag “N”)
9.Close Order
This is last step of Order Processing . In this stage only oe_order_lines_all table get updated.
These are the table get affected in this step.
•oe_order_lines_all (flow_status_code ‘closed’,open_flag “N”)
These are the typically data flow of a order to cash model for a standard order.
1. Order Entry
This is first stage when Order in enter in system.When the order is entered it basically create a record in order headers and Order Lines table.
•oe_order_headers_all (Here the flow_status_code as entered)
•oe_order_lines_all (flow_status_code as entered) ( order number is generated)
2.Order Booking
This is next stage , when Order which is entered in step 1 is booked and Flow status changed from Entered to Booked.At this stage , these table get affected.
•oe_order_headers_all (flow_status_code as booked ,booked_flag updated)
•oe_order_lines_all (flow_status_code as awaiting shipping, booked_flag updated)
•wsh_new_deliveries (status_code OP open)
•wsh_delivery_details (released_status ‘R’ ready to release)
Same time, Demand interface program runs in background And insert into inventory tables mtl_demand
3. Reservation
This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved.Once this program get successfully get completed , the mtl_reservations table get updated.
4. Pick Release
Ideally pick release is the process which is defined in which the items on the sales order are taken out from inventory.
Normally pick release SRS program runs in background . Once the program get completed these are the table get affected:
•oe_order_lines_all (flow_status_code ‘PICKED’ )
•wsh_delivery_details (released_status ‘S’ ‘submitted for release’ )
•mtl_txn_request_headers
•mtl_txn_request_lines
(move order tables.Here request is generated to move item from saleble to staging sub inventory)
•Mtl_material_transactions_temp (link to above tables through move_order_header_id/line_id
5.Pick Confirm
Items are transferred from saleble to staging Subinventory.
•mtl_material_transactions
•mtl_transaction_accounts
•wsh_delivery_details (released_status ‘Y’‘Released’ )
•wsh_delivery_assignments
6.Ship Confirm
Here ship confirm interface program runs in background . Data removed from wsh_new_deliveries
•oe_order_lines_all (flow_status_code ‘shipped’)
•wsh_delivery_details (released_status ‘C’ ‘Shipped’)
•mtl_transaction_interface
•mtl_material_transactions(linked through Transaction source header id)
•mtl_transaction_accounts
•Data deleted from mtl_demand,mtl_reservations
•Item deducted from mtl_onhand_quantities
7.Enter Invoice
This is also called Receivables interface, that mean information moved to accounting area for invoicing details.
•Invoicing workflow activity transfers shipped item information to Oracle Receivables.
•ra_interface_lines_all (interface table into which the data is transferred from order management)T
•Then Autoinvoice program imports data from this
•Table which get affected into this stage are recievables base table.
ra_customer_trx_all (cust_trx_id is primary key to link it to trx_lines table and trx_number is the invoice number)
ra_customer_trx_lines_all (line_attribute_1 and line_attribute_6 are linked to header_id (or order number) and line_id of the orders)
8.Complete Line
In this stage order line leval table get updated with Flow status and open flag.
•oe_order_lines_all (flow_status_code ‘shipped’, open_flag “N”)
9.Close Order
This is last step of Order Processing . In this stage only oe_order_lines_all table get updated.
These are the table get affected in this step.
•oe_order_lines_all (flow_status_code ‘closed’,open_flag “N”)
These are the typically data flow of a order to cash model for a standard order.
Friday, February 18, 2011
Oracle XML Publisher Bursting Sample Program
xapi:delivery - Explains where the output should be delivered
xapi:filesystem - explains the output is destined to filesystem
xapi:template - specifies the template shortcode prefixed with app short code, for eg: AR.INVOICE - AR-app short code and INVOICE is the xml template short code.
location="xdo://AR.INVOICE.en.US/?getSource=true"
Above line is very important, it takes care of pulling the latest template uploaded in the XML Publisher Administrator responsibility, you don't need to put your template hanging in any UNIX servers.
en - Language
US - Territory code (when you create template definition, you make sure that you select United States or other country)
The same above example holds good for sending the output through email, fax, etc.,
By: chandramouly
Tuesday, February 15, 2011
How to Create a Barcode in Oracle Reports
How to Create a Barcode in Oracle Reports please view below link
http://www.youtube.com/watch?v=vvQOp1YmOIc
http://www.youtube.com/watch?v=vvQOp1YmOIc
Thursday, February 10, 2011
views in Oracle Property Manager Module
List of views in Oracle Property Manager Module
PN_ACT_RENT_DETAILS_V
PN_ADDRESSES_ALL_DFV
PN_ADJUSTMENT_DETAILS_V
PN_AEL_BILL_GL_V
PN_AEL_PAY_GL_V
PN_BUILDINGS_V
PN_COMPANIES_ALL_DFV
PN_COMPANIES_V
PN_COMPANY_SITES_ALL_DFV
PN_COMPANY_SITES_V
PN_CONTACT_ASSIGN_HISTORY_V
PN_CONTACT_ASSIGNMENTS_ALL_DFV
PN_CONTACT_ASSIGNMENTS_V
PN_CONTACTS_ALL_DFV
PN_CONTACTS_V
PN_DISTRIBUTIONS_ALL_DFV
PN_DISTRIBUTIONS_HISTORY_V
PN_DISTRIBUTIONS_V
PN_EXCLUDE_TERMS_V
PN_EXP_PAYMENT_ITEMS_V
PN_FLOORS_V
PN_FOR_RENT_DETAILS_V
PN_GL_PERIOD_V
PN_INDEX_EXCLUDE_TERM_V
PN_INDEX_HISTORY_HEADERS_DFV
PN_INDEX_HISTORY_HEADERS_V
PN_INDEX_HISTORY_LINES_V
PN_INDEX_LEASE_CONSTRAINTS_DFV
PN_INDEX_LEASE_CONSTRAINTS_V
PN_INDEX_LEASE_PERIODS_ALL_DFV
PN_INDEX_LEASE_PERIODS_V
PN_INDEX_LEASES_ALL_DFV
PN_INDEX_LEASES_V
PN_INSUR_REQUIRE_HISTORY_V
PN_INSURANCE_REQUIREMENTS__DFV
PN_INSURANCE_REQUIREMNTS_V
PN_JE_CATEGORIES_V
PN_LANDLORD_SERVICE_HISTORY_V
PN_LANDLORD_SERVICES_ALL_DFV
PN_LANDLORD_SERVICES_V
PN_LEASE_CHANGES_ALL_DFV
PN_LEASE_CHANGES_V
PN_LEASE_CONTACT_ASSIGN_V
PN_LEASE_DETAILS_HISTORY_V
PN_LEASE_DETAILS_V
PN_LEASE_MILESTONES_ALL_DFV
PN_LEASE_MILESTONES_V
PN_LEASE_TRANSACTIONS_V
PN_LEASES_V
PN_LOC_ACC_MAP_HDR_V
PN_LOC_ACC_MAP_V
PN_LOCATION_CONTACT_ASSIGN_V
PN_LOCATION_FEATURES_ALL_DFV
PN_LOCATION_FEATURES_V
PN_LOCATION_PARKS_DFV
PN_LOCATION_PARKS_VL
PN_LOCATIONS_ALL_DFV
PN_LOCATIONS_ITF_V
PN_LOCATIONS_PUB_V
PN_LOCATIONS_V
PN_NOTE_DETAILS_VL
PN_NOTE_HEADERS_V
PN_OFFICES_V
PN_OPTIONS_ALL_DFV
PN_OPTIONS_HISTORY_V
PN_OPTIONS_V
PN_PAY_GROUP_BYS_V
PN_PAY_GROUP_RULES_V
PN_PAYMENT_ITEMS_V
PN_PAYMENT_SCHEDULES_ALL_DFV
PN_PAYMENT_SCHEDULES_V
PN_PAYMENT_TERMS_ALL_DFV
PN_PAYMENT_TERMS_HISTORY_V
PN_PAYMENT_TERMS_V
PN_PBUILDING_V
PN_PHONES_ALL_DFV
PN_PHONES_V
PN_PLAND_V
PN_PROPERTIES_ALL_DFV
PN_PROPERTIES_V
PN_RIGHTS_ALL_DFV
PN_RIGHTS_HISTORY_V
PN_RIGHTS_V
PN_SET_MILESTONES_VL
PN_SET_TYPES_VL
PN_SPACE_ALLOCATIONS_ALL_DFV
PN_SPACE_ALLOCATIONS_V
PN_SPACE_ASSIGN_CUST_ALL_DFV
PN_SPACE_ASSIGN_CUST_PUB_V
PN_SPACE_ASSIGN_CUST_V
PN_SPACE_ASSIGN_EMP_ALL_DFV
PN_SPACE_ASSIGN_EMP_PUB_V
PN_SPACE_ASSIGN_EMP_V
PN_TENANCIES_ALL_DFV
PN_TENANCIES_HISTORY_V
PN_TENANCIES_V
PN_TERM_HISTORY_HEADER_V
PN_TERM_TEMPLATES_ALL_DFV
PN_TERM_TEMPLATES_V
PN_TRX_LEASE_EXPENSE
PN_TRX_LEASE_REVENUE
PN_VAR_ABAT_DEFAULTS_V
PN_VAR_BKDT_DEFAULTS_V
PN_VAR_BKHD_DEFAULTS_V
PN_VAR_BKPTS_DET_ALL_DFV
PN_VAR_BKPTS_DET_V
PN_VAR_BKPTS_HEAD_ALL_DFV
PN_VAR_BKPTS_HEAD_V
PN_VAR_CONSTR_DEFAULTS_V
PN_VAR_CONSTRAINTS_ALL_DFV
PN_VAR_CONSTRAINTS_V
PN_VAR_DEDUCTIONS_ALL_DFV
PN_VAR_DEDUCTIONS_V
PN_VAR_LINE_DEFAULTS_V
PN_VAR_LINE_TEMPLATES_V
PN_VAR_LINES_ALL_DFV
PN_VAR_LINES_DATES_V
PN_VAR_LINES_V
PN_VAR_PERIODS_ALL_DFV
PN_VAR_PERIODS_DATES_V
PN_VAR_PERIODS_V
PN_VAR_RENT_ADJ_V
PN_VAR_RENT_DATES_V
PN_VAR_RENT_DETAILS_V
PN_VAR_RENT_INV_ALL_DFV
PN_VAR_RENT_INV_V
PN_VAR_RENT_SUMM_V
PN_VAR_RENTS_ALL_DFV
PN_VAR_RENTS_V
PN_VAR_SUMM_DET_V
PN_VAR_TEMPLATES_V
PN_VAR_TERMS_V
PN_VAR_VOL_HIST_ALL_DFV
PN_VAR_VOL_HIST_V
PN_XLA_EXTRACT_HEADERS_V
PN_XLA_EXTRACT_LINES_V
PN_ACT_RENT_DETAILS_V
PN_ADDRESSES_ALL_DFV
PN_ADJUSTMENT_DETAILS_V
PN_AEL_BILL_GL_V
PN_AEL_PAY_GL_V
PN_BUILDINGS_V
PN_COMPANIES_ALL_DFV
PN_COMPANIES_V
PN_COMPANY_SITES_ALL_DFV
PN_COMPANY_SITES_V
PN_CONTACT_ASSIGN_HISTORY_V
PN_CONTACT_ASSIGNMENTS_ALL_DFV
PN_CONTACT_ASSIGNMENTS_V
PN_CONTACTS_ALL_DFV
PN_CONTACTS_V
PN_DISTRIBUTIONS_ALL_DFV
PN_DISTRIBUTIONS_HISTORY_V
PN_DISTRIBUTIONS_V
PN_EXCLUDE_TERMS_V
PN_EXP_PAYMENT_ITEMS_V
PN_FLOORS_V
PN_FOR_RENT_DETAILS_V
PN_GL_PERIOD_V
PN_INDEX_EXCLUDE_TERM_V
PN_INDEX_HISTORY_HEADERS_DFV
PN_INDEX_HISTORY_HEADERS_V
PN_INDEX_HISTORY_LINES_V
PN_INDEX_LEASE_CONSTRAINTS_DFV
PN_INDEX_LEASE_CONSTRAINTS_V
PN_INDEX_LEASE_PERIODS_ALL_DFV
PN_INDEX_LEASE_PERIODS_V
PN_INDEX_LEASES_ALL_DFV
PN_INDEX_LEASES_V
PN_INSUR_REQUIRE_HISTORY_V
PN_INSURANCE_REQUIREMENTS__DFV
PN_INSURANCE_REQUIREMNTS_V
PN_JE_CATEGORIES_V
PN_LANDLORD_SERVICE_HISTORY_V
PN_LANDLORD_SERVICES_ALL_DFV
PN_LANDLORD_SERVICES_V
PN_LEASE_CHANGES_ALL_DFV
PN_LEASE_CHANGES_V
PN_LEASE_CONTACT_ASSIGN_V
PN_LEASE_DETAILS_HISTORY_V
PN_LEASE_DETAILS_V
PN_LEASE_MILESTONES_ALL_DFV
PN_LEASE_MILESTONES_V
PN_LEASE_TRANSACTIONS_V
PN_LEASES_V
PN_LOC_ACC_MAP_HDR_V
PN_LOC_ACC_MAP_V
PN_LOCATION_CONTACT_ASSIGN_V
PN_LOCATION_FEATURES_ALL_DFV
PN_LOCATION_FEATURES_V
PN_LOCATION_PARKS_DFV
PN_LOCATION_PARKS_VL
PN_LOCATIONS_ALL_DFV
PN_LOCATIONS_ITF_V
PN_LOCATIONS_PUB_V
PN_LOCATIONS_V
PN_NOTE_DETAILS_VL
PN_NOTE_HEADERS_V
PN_OFFICES_V
PN_OPTIONS_ALL_DFV
PN_OPTIONS_HISTORY_V
PN_OPTIONS_V
PN_PAY_GROUP_BYS_V
PN_PAY_GROUP_RULES_V
PN_PAYMENT_ITEMS_V
PN_PAYMENT_SCHEDULES_ALL_DFV
PN_PAYMENT_SCHEDULES_V
PN_PAYMENT_TERMS_ALL_DFV
PN_PAYMENT_TERMS_HISTORY_V
PN_PAYMENT_TERMS_V
PN_PBUILDING_V
PN_PHONES_ALL_DFV
PN_PHONES_V
PN_PLAND_V
PN_PROPERTIES_ALL_DFV
PN_PROPERTIES_V
PN_RIGHTS_ALL_DFV
PN_RIGHTS_HISTORY_V
PN_RIGHTS_V
PN_SET_MILESTONES_VL
PN_SET_TYPES_VL
PN_SPACE_ALLOCATIONS_ALL_DFV
PN_SPACE_ALLOCATIONS_V
PN_SPACE_ASSIGN_CUST_ALL_DFV
PN_SPACE_ASSIGN_CUST_PUB_V
PN_SPACE_ASSIGN_CUST_V
PN_SPACE_ASSIGN_EMP_ALL_DFV
PN_SPACE_ASSIGN_EMP_PUB_V
PN_SPACE_ASSIGN_EMP_V
PN_TENANCIES_ALL_DFV
PN_TENANCIES_HISTORY_V
PN_TENANCIES_V
PN_TERM_HISTORY_HEADER_V
PN_TERM_TEMPLATES_ALL_DFV
PN_TERM_TEMPLATES_V
PN_TRX_LEASE_EXPENSE
PN_TRX_LEASE_REVENUE
PN_VAR_ABAT_DEFAULTS_V
PN_VAR_BKDT_DEFAULTS_V
PN_VAR_BKHD_DEFAULTS_V
PN_VAR_BKPTS_DET_ALL_DFV
PN_VAR_BKPTS_DET_V
PN_VAR_BKPTS_HEAD_ALL_DFV
PN_VAR_BKPTS_HEAD_V
PN_VAR_CONSTR_DEFAULTS_V
PN_VAR_CONSTRAINTS_ALL_DFV
PN_VAR_CONSTRAINTS_V
PN_VAR_DEDUCTIONS_ALL_DFV
PN_VAR_DEDUCTIONS_V
PN_VAR_LINE_DEFAULTS_V
PN_VAR_LINE_TEMPLATES_V
PN_VAR_LINES_ALL_DFV
PN_VAR_LINES_DATES_V
PN_VAR_LINES_V
PN_VAR_PERIODS_ALL_DFV
PN_VAR_PERIODS_DATES_V
PN_VAR_PERIODS_V
PN_VAR_RENT_ADJ_V
PN_VAR_RENT_DATES_V
PN_VAR_RENT_DETAILS_V
PN_VAR_RENT_INV_ALL_DFV
PN_VAR_RENT_INV_V
PN_VAR_RENT_SUMM_V
PN_VAR_RENTS_ALL_DFV
PN_VAR_RENTS_V
PN_VAR_SUMM_DET_V
PN_VAR_TEMPLATES_V
PN_VAR_TERMS_V
PN_VAR_VOL_HIST_ALL_DFV
PN_VAR_VOL_HIST_V
PN_XLA_EXTRACT_HEADERS_V
PN_XLA_EXTRACT_LINES_V
Oracle Property Manager Tables
Oracle Property Manager Tables:
PN_ACCOUNTING_EVENTS_ALL
PN_ADDRESSES_ALL
PN_ADJUSTMENT_DETAILS
PN_ADJUSTMENT_SUMMARIES
PN_AE_HEADERS_ALL
PN_AE_LINES_ALL
PN_COMPANIES_ALL
PN_COMPANY_SITES_ALL
PN_CONTACTS_ALL
PN_CONTACT_ASSIGNMENTS_ALL
PN_CONTACT_ASSIGN_HISTORY
PN_CURRENCIES
PN_DISTRIBUTIONS_ALL
PN_DISTRIBUTIONS_HISTORY
PN_EMP_SPACE_ASSIGN_ITF
PN_INDEX_EXCLUDE_TERM_ALL
PN_INDEX_HISTORY_HEADERS
PN_INDEX_HISTORY_LINES
PN_INDEX_LEASES_ALL
PN_INDEX_LEASE_CONSTRAINTS_ALL
PN_INDEX_LEASE_PERIODS_ALL
PN_INDEX_LEASE_TERMS_ALL
PN_INSURANCE_REQUIREMENTS_ALL
PN_INSUR_REQUIRE_HISTORY
PN_LANDLORD_SERVICES_ALL
PN_LANDLORD_SERVICE_HISTORY
PN_LEASES_ALL
PN_LEASE_CHANGES_ALL
PN_LEASE_DETAILS_ALL
PN_LEASE_DETAILS_HISTORY
PN_LEASE_MILESTONES_ALL
PN_LEASE_OPTIONS_ITF
PN_LEASE_TRANSACTIONS_ALL
PN_LOCATIONS_ALL
PN_LOCATIONS_ITF
PN_LOCATION_FEATURES_ALL
PN_LOCATION_PARKS
PN_LOC_ACC_MAP_ALL
PN_LOC_ACC_MAP_HDR_ALL
PN_MILESTONES_ITF
PN_NOTE_DETAILS
PN_NOTE_HEADERS
PN_OPTIONS_ALL
PN_OPTIONS_HISTORY
PN_PAYMENT_ITEMS_ALL
PN_PAYMENT_ITEMS_EFC
PN_PAYMENT_SCHEDULES_ALL
PN_PAYMENT_TERMS_ALL
PN_PAYMENT_TERMS_EFC
PN_PAYMENT_TERMS_HISTORY
PN_PAY_GROUP_BYS
PN_PAY_GROUP_RULES
PN_PHONES_ALL
PN_PROPERTIES_ALL
PN_REC_AGREEMENTS_ALL
PN_REC_AGR_LINABAT_ALL
PN_REC_AGR_LINAREA_ALL
PN_REC_AGR_LINCONST_ALL
PN_REC_AGR_LINES_ALL
PN_REC_AGR_LINEXP_ALL
PN_REC_ARCL_ALL
PN_REC_ARCL_DTLLN_ALL
PN_REC_ARCL_DTL_ALL
PN_REC_ARCL_EXC_ALL
PN_REC_CALC_PERIODS_ALL
PN_REC_EXPCL_ALL
PN_REC_EXPCL_DTLACC_ALL
PN_REC_EXPCL_DTLLN_ALL
PN_REC_EXPCL_DTL_ALL
PN_REC_EXPCL_INC_ALL
PN_REC_EXPCL_TYPE_ALL
PN_REC_EXP_ITF
PN_REC_EXP_LINE_ALL
PN_REC_EXP_LINE_DTL_ALL
PN_REC_LINBILL_ALL
PN_REC_PERIOD_BILL_ALL
PN_REC_PERIOD_LINES_ALL
PN_RENT_ROLL_LEASE_EXP_ITF
PN_RIGHTS_ALL
PN_RIGHTS_HISTORY
PN_SET_MILESTONES
PN_SET_TYPES
PN_SPACE_ALLOCATIONS_ALL
PN_SPACE_ALLOC_ITF
PN_SPACE_ASSIGN_CUST_ALL
PN_SPACE_ASSIGN_EMP_ALL
PN_SPACE_ASSIGN_LEASE_ITF
PN_SPACE_ASSIGN_LOC_ITF
PN_SPACE_UTIL_LEASE_ITF
PN_SPACE_UTIL_LOC_ITF
PN_SYSTEM_SETUP_OPTIONS
PN_TENANCIES_ALL
PN_TENANCIES_HISTORY
PN_TERM_TEMPLATES_ALL
PN_VAR_ABATEMENTS_ALL
PN_VAR_BKPTS_DET_ALL
PN_VAR_BKPTS_HEAD_ALL
PN_VAR_CONSTRAINTS_ALL
PN_VAR_DEDUCTIONS_ALL
PN_VAR_GRP_DATES_ALL
PN_VAR_LINES_ALL
PN_VAR_PERIODS_ALL
PN_VAR_RENTS_ALL
PN_VAR_RENT_DATES_ALL
PN_VAR_RENT_INV_ALL
PN_VAR_RENT_SUMM_ALL
PN_VAR_VOL_HIST_ALL
PN_VOL_HIST_BATCH_ITF
PN_VOL_HIST_LINES_ITF
PN_ACCOUNTING_EVENTS_ALL
PN_ADDRESSES_ALL
PN_ADJUSTMENT_DETAILS
PN_ADJUSTMENT_SUMMARIES
PN_AE_HEADERS_ALL
PN_AE_LINES_ALL
PN_COMPANIES_ALL
PN_COMPANY_SITES_ALL
PN_CONTACTS_ALL
PN_CONTACT_ASSIGNMENTS_ALL
PN_CONTACT_ASSIGN_HISTORY
PN_CURRENCIES
PN_DISTRIBUTIONS_ALL
PN_DISTRIBUTIONS_HISTORY
PN_EMP_SPACE_ASSIGN_ITF
PN_INDEX_EXCLUDE_TERM_ALL
PN_INDEX_HISTORY_HEADERS
PN_INDEX_HISTORY_LINES
PN_INDEX_LEASES_ALL
PN_INDEX_LEASE_CONSTRAINTS_ALL
PN_INDEX_LEASE_PERIODS_ALL
PN_INDEX_LEASE_TERMS_ALL
PN_INSURANCE_REQUIREMENTS_ALL
PN_INSUR_REQUIRE_HISTORY
PN_LANDLORD_SERVICES_ALL
PN_LANDLORD_SERVICE_HISTORY
PN_LEASES_ALL
PN_LEASE_CHANGES_ALL
PN_LEASE_DETAILS_ALL
PN_LEASE_DETAILS_HISTORY
PN_LEASE_MILESTONES_ALL
PN_LEASE_OPTIONS_ITF
PN_LEASE_TRANSACTIONS_ALL
PN_LOCATIONS_ALL
PN_LOCATIONS_ITF
PN_LOCATION_FEATURES_ALL
PN_LOCATION_PARKS
PN_LOC_ACC_MAP_ALL
PN_LOC_ACC_MAP_HDR_ALL
PN_MILESTONES_ITF
PN_NOTE_DETAILS
PN_NOTE_HEADERS
PN_OPTIONS_ALL
PN_OPTIONS_HISTORY
PN_PAYMENT_ITEMS_ALL
PN_PAYMENT_ITEMS_EFC
PN_PAYMENT_SCHEDULES_ALL
PN_PAYMENT_TERMS_ALL
PN_PAYMENT_TERMS_EFC
PN_PAYMENT_TERMS_HISTORY
PN_PAY_GROUP_BYS
PN_PAY_GROUP_RULES
PN_PHONES_ALL
PN_PROPERTIES_ALL
PN_REC_AGREEMENTS_ALL
PN_REC_AGR_LINABAT_ALL
PN_REC_AGR_LINAREA_ALL
PN_REC_AGR_LINCONST_ALL
PN_REC_AGR_LINES_ALL
PN_REC_AGR_LINEXP_ALL
PN_REC_ARCL_ALL
PN_REC_ARCL_DTLLN_ALL
PN_REC_ARCL_DTL_ALL
PN_REC_ARCL_EXC_ALL
PN_REC_CALC_PERIODS_ALL
PN_REC_EXPCL_ALL
PN_REC_EXPCL_DTLACC_ALL
PN_REC_EXPCL_DTLLN_ALL
PN_REC_EXPCL_DTL_ALL
PN_REC_EXPCL_INC_ALL
PN_REC_EXPCL_TYPE_ALL
PN_REC_EXP_ITF
PN_REC_EXP_LINE_ALL
PN_REC_EXP_LINE_DTL_ALL
PN_REC_LINBILL_ALL
PN_REC_PERIOD_BILL_ALL
PN_REC_PERIOD_LINES_ALL
PN_RENT_ROLL_LEASE_EXP_ITF
PN_RIGHTS_ALL
PN_RIGHTS_HISTORY
PN_SET_MILESTONES
PN_SET_TYPES
PN_SPACE_ALLOCATIONS_ALL
PN_SPACE_ALLOC_ITF
PN_SPACE_ASSIGN_CUST_ALL
PN_SPACE_ASSIGN_EMP_ALL
PN_SPACE_ASSIGN_LEASE_ITF
PN_SPACE_ASSIGN_LOC_ITF
PN_SPACE_UTIL_LEASE_ITF
PN_SPACE_UTIL_LOC_ITF
PN_SYSTEM_SETUP_OPTIONS
PN_TENANCIES_ALL
PN_TENANCIES_HISTORY
PN_TERM_TEMPLATES_ALL
PN_VAR_ABATEMENTS_ALL
PN_VAR_BKPTS_DET_ALL
PN_VAR_BKPTS_HEAD_ALL
PN_VAR_CONSTRAINTS_ALL
PN_VAR_DEDUCTIONS_ALL
PN_VAR_GRP_DATES_ALL
PN_VAR_LINES_ALL
PN_VAR_PERIODS_ALL
PN_VAR_RENTS_ALL
PN_VAR_RENT_DATES_ALL
PN_VAR_RENT_INV_ALL
PN_VAR_RENT_SUMM_ALL
PN_VAR_VOL_HIST_ALL
PN_VOL_HIST_BATCH_ITF
PN_VOL_HIST_LINES_ITF
Friday, February 4, 2011
Combining and Compressing Files at UNIX
Combining and Compressing Files
Create a tarfile command : tar cf file.tar file1 file2 ...fileN
tar combines files but does not compress
Create a zipfile zip filename
Unzip a file unzip filename
In Unix, how do I create or decompress zip files?
To create a zip file, at the Unix prompt, enter:
zip filename inputfile1 inputfile2
Replace filename with the name you want to give the zip file. The .zip extension is automatically appended to the end of the filename.
Replace inputfile1 and inputfile2 with the names of the files you wish to include in the zip archive. You can include any number of files here,
or you may use an asterisk (*) to include all files in the current directory.
To include the contents of a directory or directories in a zip archive, use the -r flag:
zip -r filename directory
Replace directory with the name of the directory you want to include. This will create the archive filename.zip that contains the files and
subdirectories of directory.
Files created by zip can normally be decoded by programs such as WinZip and StuffIt Expander.
To decompress a zip file in Unix, use the unzip command. At the Unix prompt, enter:
unzip filename Replace filename with the name of the zip archive.
For more information about zip and unzip, see their manual pages:
man zip
man unzip
Create a tarfile command : tar cf file.tar file1 file2 ...fileN
tar combines files but does not compress
Create a zipfile zip filename
Unzip a file unzip filename
In Unix, how do I create or decompress zip files?
To create a zip file, at the Unix prompt, enter:
zip filename inputfile1 inputfile2
Replace filename with the name you want to give the zip file. The .zip extension is automatically appended to the end of the filename.
Replace inputfile1 and inputfile2 with the names of the files you wish to include in the zip archive. You can include any number of files here,
or you may use an asterisk (*) to include all files in the current directory.
To include the contents of a directory or directories in a zip archive, use the -r flag:
zip -r filename directory
Replace directory with the name of the directory you want to include. This will create the archive filename.zip that contains the files and
subdirectories of directory.
Files created by zip can normally be decoded by programs such as WinZip and StuffIt Expander.
To decompress a zip file in Unix, use the unzip command. At the Unix prompt, enter:
unzip filename Replace filename with the name of the zip archive.
For more information about zip and unzip, see their manual pages:
man zip
man unzip
Subscribe to:
Posts (Atom)