Happy to Help in Oracle eBussiness Suite

How to check RTP stuck records?

2016-07-06T23:59:00.001+05:30

Query for -- How to check RTP (Receiving Open Interface (ROI) Transactions) stuck records.....

SELECT RTI.*, rti.DESTINATION_TYPE_CODE,
rti.interface_transaction_id -- 'interface_transaction_id'
, rhi.header_interface_id -- 'header_interface_id'
, mp.ORGANIZATION_CODE -- 'Org'
, rti.source_document_code -- 'Type'
, pha.segment1 -- 'PO Number'
, pla.line_num -- 'PO Line'
, to_char(rti.transaction_date, 'dd-MON-yyyy') -- 'Transaction Date'
, rti.transaction_type
, rti.PROCESSING_MODE_CODE
, rti.processing_status_code
, rhi.processing_status_code
, to_char(rti.creation_date, 'dd-MON-yyyy') creation_date
, rti.quantity
, msi.segment1 -- 'Item'
, msi.item_type -- 'Item Type'
, rti.item_description
, poh.error_message
, replace(poh.error_message,chr(10),' ') ,msi.organization_id--,poh.*
--, replace(pot.error_message,chr(10),' ')
FROM po.po_interface_errors poh
, po.po_interface_errors pot
, po.rcv_transactions_interface rti
, po.rcv_headers_interface rhi
, inv.mtl_parameters mp
, po.po_headers_all pha
, po.po_lines_all pla
, inv.mtl_system_items_b msi
WHERE 1=1
AND rti.header_interface_id = rhi.header_interface_id (+)
AND rti.interface_transaction_id = pot.interface_transaction_id (+)
AND rhi.header_interface_id = poh.interface_header_id (+)
-- mapping to PO
AND rti.po_header_id = pha.po_header_id AND pha.org_id=8527
AND rti.po_header_id = pla.po_header_id
AND rti.po_line_id = pla.po_line_id
-- mapping to item
AND mp.organization_id = msi.organization_id AND msi.organization_id=106
AND rti.item_id = msi.inventory_item_id
and pha.segment1='1008007890' --in ('20231192' ,'20230863')-- PUT PO NUMBER HERE
--AND pla.line_num IN (14,57)
Order by mp.ORGANIZATION_CODE, pha.segment1, pla.line_num

Profile Option Values for Responsibility

2016-07-06T23:56:00.000+05:30

Profile Option Values for Responsibility

SELECT resp.responsibility_name,
SUBSTR (pro1.user_profile_option_name, 1, 60) Profile,
DECODE (pov.level_id,
10001,
'Site',
10002,
'Application',
10003,
'Resp',
10004,
'User')
Option_Level,
DECODE (pov.level_id,
10001,
'Site',
10002,
appl.application_short_name,
10003,
resp.responsibility_name,
10004,
u.user_name)
Level_Value,
NVL (pov.profile_option_value, 'Is Null') Profile_option_Value
FROM fnd_profile_option_values pov,
fnd_responsibility_tl resp,
fnd_application appl,
fnd_user u,
fnd_profile_options pro,
fnd_profile_options_tl pro1
WHERE 1 = 1
--AND pro1.user_profile_option_name LIKE ('%MO: Operating Unit%')
AND pro.profile_option_name = pro1.profile_option_name
AND pro.profile_option_id = pov.profile_option_id
AND UPPER (resp.responsibility_name) LIKE
UPPER ('%INV') /* comment this line if you need to check profiles for all responsibilities */
AND pov.level_value = resp.responsibility_id(+)
AND pov.level_value = appl.application_id(+)
AND pov.level_value = u.user_id(+) and resp.language='US' and pro1.language='US'
ORDER BY 1, 2

Query for Open PO - Purchase Order with Item Attachments

2016-07-06T23:48:00.001+05:30

Query for Open PO - Purchase Order with Item Attachments

SELECT line_num,item_number,PO_NUMBER ,
item_description,
unit_meas_lookup_code,quantity, unit_price,
line_status, po_line_id from (
SELECT DISTINCT PLV.line_num, msi1.segment1 item_number, (select segment1 from po_headers where po_header_id= PLV.po_header_id) PO_NUMBER,
PLV.item_description item_description,
PLV.unit_meas_lookup_code, PLV.quantity, PLV.unit_price,
NVL (PLV.closed_code, 'OPEN') line_status, PLV.po_line_id
FROM po_lines PLV,
( SELECT msi.*
FROM apps.fnd_document_datatypes dat,
apps.fnd_document_entities_tl det,
apps.fnd_documents_tl dt,
apps.fnd_documents d,
apps.fnd_attached_documents ad,
apps.fnd_documents_long_text fdlt,
apps.fnd_documents_short_text fdst,
apps.mtl_system_items_b msi
WHERE d.document_id = ad.document_id
AND dt.document_id = d.document_id
AND dt.LANGUAGE = USERENV('LANG')
AND d.datatype_id = dat.datatype_id
AND dat.LANGUAGE = USERENV('LANG')
AND ad.entity_name = det.data_object_code
AND det.LANGUAGE = USERENV('LANG')
AND ad.pk1_value = msi.organization_id
AND ad.pk2_value = msi.inventory_item_id
--AND dt.description = v_chr_desc --TRIM(rec_attach.description)
AND dt.media_id = fdlt.media_id(+)
AND dt.media_id = fdst.media_id(+)
AND ad.entity_name = 'MTL_SYSTEM_ITEMS'
AND dat.user_name IN ('Long Text', 'Short Text')
AND dt.description ='ATTACHMET'
AND msi.inventory_item_status_code ='Active'
-- AND msi.segment1 = 'VE3006'
AND msi.organization_id = 701) msi1,
financials_system_parameters fsp,
po_line_locations pllv,
po_distributions pdv,
org_organization_definitions ood
WHERE msi1.inventory_item_id = PLV.item_id
AND NVL (msi1.organization_id, fsp.inventory_organization_id) = fsp.inventory_organization_id
AND PLV.po_line_id = pllv.po_line_id
AND PLV.po_line_id = pdv.po_line_id
AND pllv.line_location_id = pdv.line_location_id
AND ood.organization_id = pllv.ship_to_organization_id
AND NVL (PLV.closed_code, 'OPEN') NOT IN
('FINALLY CLOSED', 'CLOSED')
AND NVL (PLV.cancel_flag, 'N') = 'N'
AND EXISTS (
SELECT 'x'
FROM po_line_locations pll
WHERE pll.po_line_id = PLV.po_line_id
AND NVL (pll.closed_code, 'OPEN') NOT IN
('FINALLY CLOSED', 'CLOSED')
AND NVL (pll.cancel_flag, 'N') = 'N'
AND pll.quantity - pll.quantity_received >
pll.qty_rcv_tolerance))

Receiving Transaction Processor Errors

2016-07-06T23:21:00.002+05:30

+---------------------------------------------------------------------------+

RVCTP module: Receiving Transaction Processor
+---------------------------------------------------------------------------+

Current system time is ......

+---------------------------------------------------------------------------+

RVTUQ-010: Subroutine rvtuqsurcv() returned error

Cause: Subroutine rvtuqsurcv() returned an internal error.

Action: Note this error number and the actions you are trying to perform. Contact your system administrator for
RVTUQ-040: ORA-20001: APP--20110: Encountered the error in trigger JAI_RCV_RSL_AR

Cause: A SQL error has occurred in RVTUQ. The SQL error is &LSQL_ERR.

Action: Take note of both this error number and the actions you were t
Cannot read value for profile option INV_DEBUG_TRACE in routine &ROUTINE.
Concurrent program RVCTP returned error

Cause: RVCTP encountered an internal error. Parameters: RVCTP,IMMEDIATE,37169,

Action: Note both this error number and the actions you are trying to perform. Contact your system adm
RVTBM-008: Subroutine rvtbm_immediate_mngr() returned error

Cause: Subroutine rvtbm_immediate_mngr() returned an internal error.

Action: Note this error number and the actions you are trying to perform. Contact your syste
RVTBM-120: Subroutine rvtptcontrol() returned error

Cause: Subroutine rvtptcontrol() returned an internal error.

Action: Note this error number and the actions you are trying to perform. Contact your system administrator
Cannot read value for profile option INV_DEBUG_TRACE in routine &ROUTINE.
Concurrent Manager encountered an error while attempting to start your immediate concurrent program RVCTP. Routine &ROUTINE received a return code of failure.

Executing request completion options...

Complete Oracle Apps API list

2016-01-18T23:12:00.003+05:30

Complete API list ::

HL_DI_DOC_INDEX_PUB
Document
This is the public interface to create and modify documents and its associated suppliers and recipients.
AHL_DI_DOC_REVISION_PUB
Document Revision
This is the public interface to create, modify and delete document revisions.
AHL_DI_SUBSCRIPTION_PUB
Document Subscription
This is the public interface to create, modify and delete document subscriptions.
AHL_FMP_MR_PUB
Maintenance Requirement
Package containing APIs to create, update and delete Maintenance Requirement(MR) and its associations to Document References,
AHL_MC_ITEM_COMP_PUB
Master Configuration
This is the public package that handles Creation,Modification,Termination and copying of
AHL_MC_ITEMGROUP_PUB
Item Group Association
This is the public package that creates and modifies the Item groups and item-group associations for Master Configuration
AHL_MC_MASTERCONFIG_PUB
Master Configuration
This is the public package that handles creation/modification/deletion of Master Configurations
AHL_MC_NODE_PUB
Master Configuration
This is the public package that handles creation/modification/deletion of Master Configurations Nodes
AHL_OSP_ORDERS_PUB
Process OSP Order
This package provides the procedure to Validate, Insert/Update/Delete an osp order header along with its associated lines.
AHL_OSP_SHIPMENT_PUB
Process OSP Shipment
This package Contains Record types and public procedures to process shipment headers, and lines that are related to OSP Orders.
AHL_PC_ASSOCIATION_PUB
Units/Parts Association
This is the public interface to associate the Product Classification with Units/Parts
AHL_PC_HEADER_PUB
Product Classification Header
This is the public interface to create, modify and terminate Product Classifiaction Header
AHL_PC_NODE_PUB
Product Classification Nodes
This is the public interface to Create /Modify and Delete Product Classification Nodes
AHL_RM_OPERATION_PUB
Operation
Package containing APIs to create, update and delete Operations and its associated document references, resource requirements,
AHL_RM_ROUTE_PUB
Route
Package containing APIs to create, update and delete Routes and its associated document references, operations, resources
AHL_UA_FLIGHT_SCHEDULES_PUB
Unit Flight Schedules
Package containing APIs to process flight schedules and following transit visits.
AHL_UC_INSTANCE_PUB
Unit Configuration Node
This package provides the APIs for processing the node instance in a Unit Configuration.
AHL_UC_UNITCONFIG_PUB
Unit Configuration Header
This package provides the APIs for processing the Unit Configuration headers.
AHL_UC_UTILIZATION_PUB
Unit Configuration Utilization
This package provides the APIs for updating the Utilization on a Unit Configuration.
AHL_UC_VALIDATION_PUB
Unit Configuration Validation
This package provides the APIs for validating a Unit Configuration.
AHL_UMP_UF_PUB
Utilization Forecast
This is the public interface to process utilization forecast for Product Classification node, item, unit or item instance.
AHL_UMP_UNITMAINT_PUB
Maintain Unit Maintenance Plan Schedule
Builds and Calculates Unit and Item instance Maintenance Plan Schedule.
AME_API
Approval Process
This package contains APIs that a typical workflow uses to process an
AME_API2
Parallel Approval Process
This API package contains routines that a typical workflow uses to process
AME_API3
Ancillary Parallel Approvers Process
This API package contains ancillary routines.
AME_API4
Approval Process Groups
This package contains ancillary APIs.
AML_SALES_LEADS_V2_PUB
Oracle Leads Management Public Wrapper API
This package provides consolidated methods to create leads and supported
AMS_ACTMETRIC_PUB
Oracle Marketing Activity Metrics Public API
This package provides methods to create, update, or delete marketing
AMS_CAMP_SCHEDULE_PUB
Oracle Marketing Campaign Schedules Public API
This package provides methods to create or update a marketing campaign schedule.
AMS_CAMPAIGN_PUB
Oracle Marketing Campaigns Public API
This package provides methods to create or update a marketing campaign.
AMS_METRIC_PUB
Oracle Marketing Metrics Definition Public API
This package provides methods to create marketing metrics.
AP_PO_VENDORS_APIS_PKG
Suppliers Package
This Package provides APIs to allow users to import suppliers,
AR_ADJUST_PUB
Adjustment
Adjustment API allows users to create, approve, update, and reverse
AR_BR_CANCEL_UNPAID_API_PUB
Unpaid Bill
Unpaid Bill API sets the status for each unpaid bill receivable to
AR_BR_REMIT_IMPORT_API_PUB
Remittance Import
Remittance Import API allows the user to import a remittance from a
AR_CMGT_CREDIT_REQUEST_API
Credit Request Creation
The Credit Request Creation API creates a credit request in the
AR_CREDIT_MEMO_API_PUB
Credit Memo Approval and Creation
Credit Memo Approval and Creation API lets you initiate the creation
AR_DEPOSIT_API_PUB
Deposit
Deposit APIs provide an extension to existing functionality of
AR_INVOICE_API_PUB
Invoice Creation
Invoice Creation API allows users to create an invoice using simple
AR_PREPAYMENTS_PUB
Prepayment
The Prepayments API enables the creation of a receipt in advance of
AR_RECEIPT_API_PUB
Receipt
Receipt APIs provide an extension to existing
AR_REVENUEADJUST_PUB
Revenue Adjustment
Revenue Adjustment APIs allow users to recognize event-based revenue
AS_SALES_LEADS_PUB
Oracle Leads Management Public API
This package provides methods to create, update, or delete leads, lead product
BEN_CWB_AUDIT_API
Compensation Workbench Audit Log
This package contains API to generate audit log for data changes in
BEN_CWB_PERSON_GROUPS_API
Compensation Workbench Budget Detail
This package contains Compensation Workbench Budget APIs.
BEN_CWB_PERSON_INFO_API
Compensation Workbench Person Information
This package contains Compensation Workbench Person APIs.
BEN_CWB_PERSON_RATES_API
Compensation Workbench Person Award
This package contains Compensation Workbench Person Award APIs.
BEN_CWB_PERSON_TASKS_API
Compensation Workbench Person Task
This package contains Compensation Workbench Person Task APIs.
BEN_CWB_PL_DSGN_API
Compensation Workbench Plan Design
This package contains Compensation Workbench Plan Design APIs.
BEN_CWB_STOCK_OPTN_DTLS_API
Compensation Workbench Stock Option
This package contains APIs to upload third party stock option data.
BOM_IMPORT_PUB
Structure Import
This package provides APIs for importing structure and its related entities
BOM_INVOKE_BO
Invoke Business Object
API for invoking methods in BOM Public packages from a different schema.
BOM_OE_EXPLODER_PKG
Order Entry Exploder
This API contains methods for the custom bom exploder for use by Order Entry.
BOM_SECURITY_PUB
BOM Security Policy Package
This API contains methods to ensure BOM security
BOMCUMYD
Cumulative Yield calculation for Flow and Network Routing
This API contains procedures to calculate a) Cumulative Yield values for
BOMPCCLT
Lead Time Rollup calculation
This API contains procedures to calculate Cumulative Lead Times for
BOMPEXPL
Structure Exploder
This API contains methods to explode BOM based on the module passed.The possible values for module are
BOMPIMPL
Implode BOM Package
This API contains methods to implode BOM .It contains two different imploders,
BOMPXINQ
Structure Export
This API contains methods to Explode BOM and Export the data to PL/SQL tables.This method
CDR_PUB_API_INITIALIZATION
LSH API Initialization
This is a public interface that is used internally to initialize all other Oracle LSH external API packages.
CDR_PUB_ATK_ADAPTER
LSH ATK ADAPTER
This package is used to create adapter domain and adapter area for user defined adapters.
CDR_PUB_CLA_HIERARCHY_VALS
LSH CLA HIER Values
This is a public interface for classification hierarchy value-related operations.
CDR_PUB_CLA_OBJ_CLASSIFICATION
LSH Obj Classification
This is a public interface for assigning and removing object classifications.
CDR_PUB_CLA_SUBTYPES
Subtype Classification
This is a public interface for all Classification subtype functions
CDR_PUB_DEF_CONSTANTS
LSH Definition Constants
This is a public interface that hosts definition constants for LSH APIs.
CDR_PUB_DEF_FACTORY_SUPPORT
LSH Factory Support
This is a public interface that hosts utility APIs for other packages.
CDR_PUB_DEF_FACTORY_UTILS
LSH Factory Utility
This is a public interface that hosts utility APIs for other packages.
CDR_PUB_DEF_FACTORY_VALIDATE
LSH Factory Validate
This is a public interface that hosts APIs for checking object validation on various objects.
CDR_PUB_DF_APPLICATIONAREA
LSH Application Areas
This is a public interface for Application Area-related operations, including creating, modifying, and removing Application Areas, and copying and moving object definitions into an Application Area.
CDR_PUB_DF_BUSINESSAREA
Business Area
This is a public interface for Business Area-related APIs, including creating, modifying, and
CDR_PUB_DF_BUSINESSAREA_HIER
Business Area Hierarchies
This is a public interface for all Business Area Hierarchy-relatedoperations, including creating, modifying,
CDR_PUB_DF_BUSINESSAREA_JOIN
Business Area Join
This is a public interface for Business Area Join-related operations, including creating, modifying, and
CDR_PUB_DF_DATAMART
Data Marts
This is a public interface for Data Mart-related operations, including creating, modifying, and removing Data Marts.
CDR_PUB_DF_DOMAIN
LSH Domain
This is a public interface for Domain-related operations, including creating, modifying, and removing Domains.
CDR_PUB_DF_EXECUTIONSETUP
LSH Execution Setup
This is a public interface for Execution Setup-related operations, including creating, modifying, and removing Execution Setups.
CDR_PUB_DF_LOADSET
Load Sets
This is a public interface for Load Set-related operations, including creating, modifying, and removing Load Sets.
CDR_PUB_DF_MAPPING
LSH Mapping
This is a public interface for mapping-related operations, including creating and modifying mappings at the Table Descriptor and Column levels
CDR_PUB_DF_NAMING
LSH Namings
This is a public interface which hosts the Naming API to update the Version label
CDR_PUB_DF_NAMING_UTIL
LSH Naming
This is a public interface that is used to retrieve data from naming-related tables.
CDR_PUB_DF_NOTIFICATIONS
LSH Notifications
This package hosts all the Notification related APIs
CDR_PUB_DF_OWNERSHIP
LSH OwnerShip
This API is no longer available
CDR_PUB_DF_PARAM_RELATION
LSH Parameter Relations
This is a public interface for operations related to passing values from one Parameter to another within a Report Set or Workflow.
CDR_PUB_DF_PARAMETER
LSH Parameters
This is a public interface for operations involving defined Parameter objects
CDR_PUB_DF_PARAMETER_SET
LSH Parameter Sets
This is a public interface for operations involving parameter sets
CDR_PUB_DF_PLANNED_OUTPUT
LSH Planned Output
This is a public interface for all Planned Output related functions, including creating, modifying, removing, and copying Planned Outputs.
CDR_PUB_DF_PROGRAM
LSH Program
This is a public interface for Program-related functions, including creating, modifying, and removing Programs.
CDR_PUB_DF_SOURCECODE
LSH Source Code
This is a public interface for Source Code-related operations, including creating, modifying, and removing Source Code objects.
CDR_PUB_DF_TABLE
LSH Table Creation
This is a public interface for all operations related to Tables, Columns, and Constraints, including creation, deletion, modification, and checking in and out of these objects.
CDR_PUB_DF_VARIABLE
LSH Variable
This is a public interface for Variable-related operations, including creating, modifying, and removing Variables.
CDR_PUB_DF_WORKAREA
Work Areas/Check In Work Area
This is a public interface for Work Area-related operations, including creating, modifying, removing, cloning, checking in and installing Work Areas.
CDR_PUB_DF_WORKFLOW
LSH Workflow
This is a public interface for Workflow-related operations, including creating,
CDR_PUB_EXE_EXTERNAL
LSH External
This is a public interface that hosts the API to upload output CLOBs into Oracle LSH.
CDR_PUB_EXE_MSG_API
LSH Msg Submission
This package contains the API to add a submission request to the message queue.
CDR_PUB_EXE_RUNTIME
LSH Runtime
This is a public interface to retrieve information about a currently running job.
CDR_PUB_EXE_SUBMISSION
LSH Submission
This package contains a procedures to log the messages when a job is running and to create the submission record
CDR_PUB_EXE_USER_UTILS
LSH User Utils
This package contains APIs to set and retrieve the execution status of LSH Programs.
CDR_PUB_MSG_PUB
LSH Read Message
This is a public interface that is used for reporting, by reading messages from the system's message stack.
CDR_PUB_PRINT_OUTPUT
LSH Print Output
This package includes APIs for submitting a job for printing, and for retrieving the BLOB and CLOB objects associated with the CDR output.
CDR_PUB_RS_OTD_FILE
LSH OTD Files
This is a public interface used to create, modify and remove OTD files,
CDR_PUB_RS_OVERLAY_TEMPLATE
LSH Report Set Overlay Template
This package is used to create, checkin, checkout,
CDR_PUB_RS_REPORT_SET
LSH Report Sets
This is a public interface for all the Report Sets related functions.These APIs provide a number of procedures for Report Sets including Create Report Sets, Modify Report Sets, Remove Report Sets, Checkin Report Set,
CDR_PUB_RS_REPORT_SET_ENTRY
LSH Report Set Entry
This is a public interface for Report Set Entry-related operations, including creating, modifying, and removing Report Set Entries, getting information about the Report Set and Report Set Entries, and changing the Report Set structure.
CDR_PUB_SECURITY_PKG
LSH Security
This is a public interface for object security-related operations.
CDR_PUB_VL_VALIDATION
LSH Validation
This is a public interface for validation-related operations.
CN_CALC_SUBMISSION_PUB
Create/Update Calculation Submission Batch
This package provides the APIs for creating and updating a calculation submission batch.
CN_COMMISSION_CALC_PUB
Calculate Projected Compensation Public Application Program Interface
The calculate_Commission procedure in cn_commission_calc_pub is used for
CN_COMP_PLAN_PUB
Create Compensation Plan
This package is used to create compensation plans.
CN_GET_TX_DATA_PUB
Mass Adjustments
Package for Mass Adjustments (JSP Version)
CN_MULTI_RATE_SCHEDULES_PUB
Rate Tables Public Application Program Interface
This package is used to perform the following procedures related to rate tables.
CN_PAYGROUP_PUB
Pay Group
The procedures in this package can be used to get pay group information, validate the input, create pay groups, update pay groups, and delete pay groups.
CN_PLAN_ELEMENT_PUB
Plan Element
The procedure in this package can be used to create, update, delete and duplicate a plan element.
CN_PMTPLAN_PUB
Payment Plan
This procedure is used to create, update, and delete payment plans.
CN_PMTSUB_PUB
Submit Payrun Concurrent Programs
This procedure is used to pay a payrun and update the subledger, run the
CN_POST_COLLECTION_TAE_PUB
OIC Integration With TA Engine Public Application Program Interface
This public package integrates the Oracle Incentive Compensation collection
CN_PRD_QUOTA_PUB
Period Quotas distribution
The procedure in this package can be used to distribute the target of the specified plan element
CN_PROCESS_TAE_TRX_PUB
Populate TAE Data in OIC Public Application Program Interface
This public package populates results from the Territory Assignment Engine
CN_ROLE_PLANS_PUB
Role to Compensation Plan Assignment
There are three APIs for CN_ROLE_PLANS_PUB.
CN_SCA_CREDITS_BATCH_PUB
Get Sales Credits Public Application Program Interface
This package is accessed by users of the Sales Credit Allocation Module via
CN_SCA_CREDITS_ONLINE_PUB
Get Sales Credits Public Application Program Interface(Online)
This package is accessed by the users of the Sales Credit Allocation module
CN_SCA_WF_CUST_PKG
Workflow Process custom Revenue Distribution
This package includes public APIs for Sales Credit Allocation custom Revenue
CN_SCA_WF_PKG
Workflow processes for Revenue Distribution and Transaction Transfer
This package includes public APIs for Sales Credit Allocation standard Revenue
CN_SRP_CUSTOMIZE_PUB
Salesperson Customization
This procedure allows user to customize the target, goal, payment amount
CN_SRP_PAYGROUP_PUB
Assign Pay Groups
The procedures in this package can be used to assign salesreps to a pay group and to update thatassignment.
CN_SRP_PMT_PLANS_PUB
Assign Payment Plans
This procedure is used to create, update, and delete payment plan assignments for salesrepsindividually or in mass.
CN_SRP_PRD_QUOTA_PUB
Salesperson Period Quotas Distribution
This procedure distributes the target for a plan element across the periods
CS_CHARGE_CREATE_ORDER_PUB
Charges
This public interface for the charges functionality in Oracle Service, provides functions that enable
CS_CHARGE_DETAILS_PUB
Service Charges
This public interface for the charges functionality in Oracle Service, provides functions
CS_INCIDENTLINKS_PUB
Service Request Link
Service Request Links provides functions to enable user to create, update and delete
CS_MULTIORG_PUB
Service Charges
This public interface for the charges functionality in Oracle Service, determines the default operating
CS_SERVICEREQUEST_PUB
Service Request
Allows users to create, update service request details, update service request status
CSD_REPAIRS_PUB
Depot Repair APIs
This is the public interface for the Depot Repair API.
CSF_DEBRIEF_PUB
Debrief
This is the public interface for Debrief transactions.
CSI_II_RELATIONSHIPS_PUB
Manage Instance-to-Instance Relationships
This is a public API for managing Instance-to-Instance Relationships.
CSI_ITEM_INSTANCE_PUB
Manage Item Instances
This is a public API for Item Instances Management.
CSI_SYSTEMS_PUB
Manage Systems
This is a public API for managing Systems.
CST_COST_API
Item Cost API
This package contains Item Cost APIs.
CZ_CF_API
Configuration API
This package provides procedures and functions to perform various operations on configurations.
CZ_MODELOPERATIONS_PUB
Model Operations API
This is the public interface to operations on configuration models and configuration UIs.
EAM_ACTIVITYSUPN_PUB
Activity Suppression
This package is used for the INSERT / UPDATE of suppression relationships of theasset activity associations.
EAM_ASSET_AREAS_PUB
Asset Areas
This package is used for the INSERT / UPDATE of asset areas.
EAM_ASSET_ROUTES_PUB
Asset Routes
This package is used for the INSERT / UPDATE of asset routes.
EAM_ASSETATTR_GRP_PUB
Asset Group and Attributes Group Association
This package is used for the INSERT / UPDATE /Validationof asset attribute groups.
EAM_ASSETATTR_VALUE_PUB
Asset Attributes Values
This package is used for the INSERT / UPDATE /Validation of asset attributevalues.
EAM_ASSETNUMBER_PUB
Asset Number
This package is used for the INSERT / UPDATE of asset numbers.
EAM_COMPLETION
Maintenance Work Completion
This is the public API for maintenance work order completion/uncompletion
EAM_DEPT_APPROVERS_PUB
Department Approvers
This package is used for the INSERT / UPDATE of Department Approvers.
EAM_ITEM_ACTIVITIES_PUB
Asset Activity
This package is used for the INSERT / UPDATE of Asset Activity association.
EAM_METER_PUB
EAM Meters
This package is used for the INSERT / UPDATE of meters.
EAM_METERASSOC_PUB
Asset Meter
This package is used for the INSERT of Asset Meters Association.
EAM_METERREADING_PUB
Meter Reading package
This package is used for creating and disabling the meter readings.
EAM_PARAMETERS_PUB
Eam Parameters
This package is used for the INSERT / UPDATE of Eam Parameters.
EAM_PMDEF_PUB
Preventive Maintenance Schedule
This package is used for the INSERT / UPDATE of PM Schedules.
EAM_PROCESS_WO_PUB
Asset Maintenance Work Order Creation
This package is used for creation of asset maintenance work order(Single/Multiple)
EAM_SETNAME_PUB
Preventive Maintenance Set Name
This package is used for the INSERT / UPDATE of PM Set Names.
EC_POAO_EXT
Purchase Order Acknowledgments Extension Columns API
This is a stub API provided as part of the Extensible Architecture feature of the Oracle e-Commerce Gateway and can be used to populate extension columns in the Purchase Order Acknowledgments EDI transaction.
EC_POCAO_EXT
Purchase Order Change Acknowledgments Extension Columns API
This is a stub API provided as part of the Extensible Architecture feature of the Oracle e-Commerce Gateway and can be used to populate extension columns in the Purchase Order Change Acknowledgments EDI transaction.
EDR_ERES_EVENT_PUB
E-records Evidence Store APIs
These APIs raise an e-signature event or related events in deferred mode.
EDR_EVENT_RELATIONSHIP_PUB
E-records Evidence Store APIs
These APIs establish relationships between e-records.
EDR_EVIDENCESTORE_PUB
E-records Evidence Store APIs
This is the public interface for the Evidence Store, and it retrieves the e-record details.
EDR_FILES_PUB
Upload and request approval for a file
This API provides a generic file upload management system for uploading files
EDR_STANDARD_PUB
Oracle E-Records Utility
This package contains all general Oracle E-Records utilities.
EDR_TRANS_ACKN_PUB
E-records Evidence Store APIs
This API is used to sends appropriate acknowledgement to evidence store
EGO_ITEM_PUB
Catalog Item Maintenance
This package provides functionality for maintaining items, item
EGO_USER_ATTRS_DATA_PUB
User-Defined Attributes
This package provides User-Defined Attributes functionality for all
ENG_ECO_PUB
Create or Update Change
Change Management solution offers mechanisms to control changes to entity such as Item.
ERROR_HANDLER
Error Handler
You can use this API for logging/retrieving Error or Warning messages.This API should be used along with
FA_ADDITION_PUB
Asset Addition API
Create asset additions.
FA_ADJUSTMENT_PUB
Adjustments API
Create asset adjustments.
FA_ASSET_DESC_PUB
Update Asset Description API
This is the public interface for the Asset, Invoice, and Retirement
FA_CIP_PUB
Capitalize/Reverse Capitalize API
Capitalizes assets and reverses capitalization of assets.
FA_DELETION_PUB
Delete Asset API
Deletes assets from the system.
FA_INV_XFR_PUB
Invoice Transfer API
Creates asset transfers.
FA_RECLASS_PUB
Reclassify Asset API
Creates asset reclassifications.
FA_RESERVE_TRANSFER_PUB
Reserve Transfer API
Transfers reserve between two group assets in the same book.
FA_RETIREMENT_ADJUSTMENT_PUB
Group Retirement Adjustment API
Performs group retirement adjustments.
FA_RETIREMENT_PUB
Retirement/Reinstatement API
Creates asset retirements, reinstatements, cancellation of retirements, and
FA_REVALUATION_PUB
Revaluation API
Creates asset revaluations.
FA_TRANSFER_PUB
Asset Transfer API
Create asset transfers.
FA_UNIT_ADJ_PUB
Unit Adjustments API
Adjusts units of an asset.
FA_UNPLANNED_PUB
Unplanned Depreciation API
Creates unplanned depreciation.
FND_CONC_GLOBAL
Concurrent Global package
This package is used for submitting sub-requests from PL/SQL concurrent programs.
FND_CONCURRENT
Request Set
Utility APIs for concurrent processing.
FND_FILE
FND File
This package contains the procedures to write text to log and output files.
FND_FLEX_EXT
Key Flexfield Validation Public
Key FlexField server side validation functions.
FND_FORM_FUNCTIONS_PKG
Form Function
Table Handler to insert or update data in FND_FORM_FUNCTIONS table.
FND_FORM_PKG
Form
Table Handler to insert or update data in FND_FORM table.
FND_GLOBAL
Application Context APIs
Application context related APIs.
FND_MENU_ENTRIES_PKG
Menu Entry
Table Handler to insert or update data in FND_MENU_ENTRIES table.
FND_MENUS_PKG
Menu
Table Handler to insert or update data in FND_MENUS table.
FND_MESSAGE
Message Dictionary
APIs to Set, Retrieve, Clear the messages in Message Stack.
FND_OBJECT_TABLESPACES_PUB
Customize Object Classification
This package contains procedures for registering or customizing
FND_PROFILE
Profile Management APIs
APIs to manipulate values stored in client
FND_PROFILE_OPTIONS_PKG
Profile Table Handler
Table Handler to Insert
FND_PROGRAM
Concurrent Program Loaders
Contains procedures for creating the concurrent program executables, concurrent programs with parameters and incompatibility rules, request sets and request groups
FND_REQUEST
Concurrent Request
Contains concurrent processing related utilities
FND_REQUEST_INFO
Request Information
Used in MLS functions to get the MLS information for a request.
FND_RESP_FUNCTIONS_PKG
Responsibility Function
Table Handler to insert or update data in FND_RESP_FUNCTIONS table.
FND_RESPONSIBILITY_PKG
Responsibility
Table Handler to insert or update data in FND_RESPONSIBILITY table.
FND_SECURITY_GROUPS_PKG
Security Group
Table Handler to insert or update data in FND_SECURITY_GROUPS table.
FND_SET
Request Set
Includes procedures for creating concurrentprogram request sets, adding programs to a request set, deletingprograms from a
FND_SSO_MANAGER
SSO Manager
This package provides APIs for central Login/Logout Management
FND_SUBMIT
Request Set Submission
This package contains the APIs for request set submission.
FND_TABLESPACES_PUB
Customize Tablespace Model
This package contains procedures for customizing tablespace model
FND_USER_PKG
User
Table Handler to insert or update data in FND_USER table.
FND_USER_RESP_GROUPS_API
User Responsibility Group
Table Handler to insert or update data in FND_USER_RESP_GROUPS table.
GHR_AGENCY_APPEALS_API
Agency Appeal
This package contains the procedures for creating, updating, and deleting
GHR_ASSIGNMENT_API
Assignment
This package contains the Assignment API.
GHR_COMPLAINANT_APPEALS_API
Complainant Appeal
This package contains the procedures for creating, updating and deleting GHR
GHR_COMPLAINT_ADRS_API
Complaint Tracking Alternate Dispute Resolution
This package contains the procedures for creating, updating, and deleting
GHR_COMPLAINT_API
Complaint
This package contains the procedures for creating and updating GHR
GHR_COMPLAINT_BASES_API
Complaint Base
This package contains the procedures for creating, updating and deleting GHR
GHR_COMPLAINT_CLAIMS_API
Complaint Claim
This package contains the procedures for creating, updating and deleting GHR
GHR_COMPLAINT_INCIDENTS_API
Complaint Incident
This package contains the procedures for creating, updating and deleting GHR
GHR_COMPLAINT_PEOPLE_API
Complaint People
This package contains the procedures for creating, updating, and deleting
GHR_COMPLAINTS_CA_DETAILS_API
Complaint Corrective Action Detail
This package contains the procedures for creating, updating, and deleting
GHR_COMPLAINTS_CA_HEADERS_API
Complaint Corrective Action Header
This package contains the procedures for creating, updating, and deleting
GHR_DUTY_STATION_API
Duty Station
This package contains Duty Station APIs.
GHR_ELEMENT_API
Request for Personnel Action Element
This API processes Federal HR elements.
GHR_NOAC_LAS_API
Nature of Action / Legal Authority Code Combination
This package contains the procedures for creating, updating, and deleting US
GHR_NOAC_REMARKS_API
Nature of Action Code /Remark combination
This package contains the procedures for creating, updating, and deleting US
GHR_PA_REMARKS_API
Request for Personnel Action Remark
This package contains the procedures for creating, updating, and deleting
GHR_PAR_EXTRA_INFO_API
Request for Personnel Action Extra Information
This package contains the procedures for creating, updating, and deleting
GHR_PDC_API
Position Description Classification
This package contains the procedures for creating, updating, and deleting
GHR_PDI_API
Position Description
This package contains the Position Description APIs.
GHR_PERSON_ADDRESS_API
Person Address
This package contains person address APIs.
GHR_POSNDT_API
Position DateTrack
This package contains the procedures for Creating Federal HR Position
GHR_SF52_API
Request for Personnel Action
This package contains the procedures for creating, updating, and deleting a
GL_JOURNAL_IMPORT_PKG
Journal Import Functions
Provides functions to use with Journal Import.
GMD_ACTIVITIES_PUB
Activities API
This interface is used to create, update and delete Activities.
GMD_FETCH_VALIDITY_RULES
Validity Rules Fetch package
This interface is used to fetch information reqd.
GMD_FORMULA_DETAIL_PUB
Formula Details API
This interface is used to create, update and delete Formula Details.
GMD_FORMULA_PUB
Formula Header API
This interface is used to create, update and delete Formula Headers.
GMD_OPERATION_ACTIVITIES_PUB
Operation Activities API
This interface is used to create, update and delete Operation Activities.
GMD_OPERATION_RESOURCES_PUB
Operation Resources API
This interface is used to create, update and delete Operation Resources.
GMD_OPERATIONS_PUB
Operation Header API
This interface is used to create, update and delete Operation Headers.
GMD_OUTBOUND_APIS_PUB
Public level outbound GMD Quality API package
This is Public level outbound GMD Quality API package
GMD_QC_TESTS_PUB
Quality Tests package
This interface is used to create and delete Test details.
GMD_RECIPE_DETAIL
Recipe Details API
This interface is used to create and update Recipe Details like Process Loss, Customers,
GMD_RECIPE_FETCH_PUB
OUTBOUND:Recipe API
This interface is used to retrieve or export Recipes data such as
GMD_RECIPE_HEADER
Recipe Header API
This interface is used to create, update and delete Recipe Headers.
GMD_RESULTS_PUB
GMD Results Package
This interface is used for processing QM Results.
GMD_ROUTING_STEPS_PUB
Routing Steps API
This package is used to create or modify Routing Steps and Dependencies.
GMD_ROUTINGS_PUB
Routings Header API
This interface is used to insert, update, delete and undelete Routing Headers.
GMD_SAMPLES_PUB
GMD Samples Package
This interface is used to create, delete, and validate samples.
GMD_SPEC_PUB
Quality Specifications package
This interface is used to create and delete Specifications.
GMD_SPEC_VRS_PUB
QC Spec Validity Rules package
This interface is used for processing QC Spec Validity Rules.
GMD_STATUS_PUB
GMD-NPD Change Status API
This interface is used to modify the Status of an entity (Formula, Recipe,
GME_API_PUB
Process Execution Public API's
Contains the headers for the Process Execution (GME) APIs in Oracle
GMF_ALLOCATIONDEFINITION_PUB
GMF Allocation Definitions API
This is the public interface for OPM Allocation Definitions API
GMF_BURDENDETAILS_PUB
GMF Burden Details API
This is the public interface for OPM Burden Details API
GMF_ITEMCOST_PUB
GMF Item Cost API
This is the public interface for OPM Item Cost API
GMF_RESOURCECOST_PUB
GMF Resource Cost API
This is the public interface for OPM Resource Cost API
GMI_OM_ALLOC_API_PUB
GMI Allocate OPM Orders API
This is the public interface for Allocating Process Order Management lines.
GMI_PICK_CONFIRM_PUB
GMI Pick Confirm OPM Orders API
This is the public interface for Pick Confirm OPM Orders API.
GMIPAPI
GMI Inventory API
This is the public interface for OPM Inventory API
GMP_CALENDAR_API
GMP_CALENDAR_API
This is the public interface for fetching data from OPM Shop Calendar
GMP_RESOURCE_DTL_PUB
GMP_RESOURCE_DTL_PUB
This is the public interface for OPM Plant Resources API
GMP_RESOURCES_PUB
GMP_RESOURCES_PUB
This is the public interface for OPM Generic Resources API
GMP_RSRC_AVL_PKG
GMP_RSRC_AVL_PKG
This is the API for Resource Availability calculations.
HR_ABSENCE_TYPE_API
Absence Attendance Type
This package contains Absence Attendance Type APIs.
HR_ACCRUAL_PLAN_API
Accrual Plan
This package contains accrual plan APIs.
HR_API_HOOK_CALL_API
API Hook Call
This package contains APIs for maintaining User Hook Calls.
HR_APPLICANT_API
Applicant
This package contains applicant APIs.
HR_APPLICATION_API
Application
This package contains applications APIs.
HR_APPRAISAL_TEMPLATES_API
Appraisal Template
This API contains Appraisal Template APIs.
HR_APPRAISALS_API
Appraisal
This package contains Appraisals APIs.
HR_ASG_BUDGET_VALUE_API
Assignment Budget Value
This package maintains assignment budget values.
HR_ASSESSMENT_GROUPS_API
Assessment Group
This package contains APIs to maintain assessment groups.
HR_ASSESSMENT_TYPES_API
Assessment Type
This package contains APIs relating to assessment types.
HR_ASSESSMENTS_API
Assessment
This package contains APIs that maintain assessments.
HR_ASSIGNMENT_API
Assignment
This package contains APIs for maintaining employee, applicant and
HR_ASSIGNMENT_EXTRA_INFO_API
Assignment Extra Information
This package contains APIs for maintaining extra information for
HR_AU_APPLICANT_API
Applicant for Australia
This package contains applicant API for Australia.
HR_AU_ASSIGNMENT_API
Assignment for Australia
This package contains assignment APIs for Australia.
HR_AU_EMPLOYEE_API
Employee for Australia
This package contains employee APIs for Australia.
HR_AU_PERSON_API
Person for Australia
This package contains person API for Australia.
HR_AU_PERSONAL_PAY_METHOD_API
Personal Payment Method for Australia
This package contains personal pay method API for Australia.
HR_AU_SUPER_API
Superannuation Contribution for Australia
This package contains superannuation contribution APIs for Australia.
HR_AU_TAX_API
Tax for Australia
This package contains tax APIs for Australia.
HR_AUTHORIA_MAPPING_API
Authoria Mapping
This package contains APIs that maintain mapping information for the
HR_BATCH_MESSAGE_LINE_API
Batch Message Line
This package contains APIs that will maintain line messages information for
HR_BE_CONTRACT_API
Employment Contract for Belgium
This package contains contract APIs for Belgium.
HR_BE_EMPLOYEE_API
Employee for Belgium
This package contains employee APIs for Belgium.
HR_CA_APPLICANT_API
Applicant for Canada
This package contains APIs used while creating Canadian Applicants.
HR_CA_ASSIGNMENT_API
Assignment for Canada
This package contains person assignment APIs.
HR_CA_EMPLOYEE_API
Employee for Canada
This package contains Canadian Employee Creation APIs.
HR_CAGR_ENT_ITEM_API
Collective Agreement Entitlement Item
This package contains APIs that maintain collective agreement entitlement
HR_CAGR_ENT_LINES_API
Collective Agreement Entitlement Line
This package contains APIs that maintain entitlement lines used by
HR_CAGR_ENTITLEMENT_API
Collective Agreement Entitlement
This package contains APIs that maintain entitlements used by collective
HR_CAGR_GRADE_STRUCTURES_API
Collective Agreement Grade Structure
This package contains APIs which maintain collective agreement grade
HR_CAGR_GRADES_API
Collective Agreement Grade
This package contains APIs which maintain collective agreement grades.
HR_CAGR_PARAMETER_API
Collective Agreement Parameter
This package contains APIs which maintain collective agreement parameters.
HR_CANCEL_HIRE_API
Cancel Hire
This package contains APIs relating to canceling employee hires.
HR_CANCEL_PLACEMENT_API
Cancel Placement
This package contains APIs relating to canceling contingent worker
HR_CHANGE_START_DATE_API
Change Start Date
This package contains the API for changing the start date of an employee or
HR_CN_APPLICANT_API
Applicant for China
This package contains applicant API for China.
HR_CN_ASSIGNMENT_API
Assignment for China
This package contains assignment APIs for China.
HR_CN_CONTACT_API
Personal Contact for China
This package contains the contact APIs for China.
HR_CN_EMPLOYEE_API
Employee for China
This package contains employee APIs for China.
HR_CN_PERSON_ADDRESS_API
Person Address for China
This package contains APIs for creation of personal addresses for China.
HR_CN_PERSON_API
Person for China
This package contains API for updation of person details for China.
HR_CN_PERSONAL_PAY_METHOD_API
Personal Payment Method for China
This package contains APIs for creation of personal payment methods for
HR_COLLECTIVE_AGREEMENT_API
Collective Agreement
This package contains APIs which maintain collective agreements.
HR_COMP_ELEMENT_OUTCOME_API
Competence Element Outcome
This package contains competence element outcome APIs.
HR_COMPETENCE_ELEMENT_API
Competence Element
This package contains Competence Element APIs.
HR_COMPETENCE_OUTCOME_API
Competence Outcome
This package contains competence outcome APIs.
HR_COMPETENCES_API
Competence
This package contains competences APIs.
HR_CONTACT_API
Personal Contact
This API creates a new contact.
HR_CONTACT_EXTRA_INFO_API
Contact Extra Information
This package contains APIs to maintain contact extra information records
HR_CONTACT_REL_API
Contact Relationship
This package contains APIs to maintain contact relationship information.
HR_CONTINGENT_WORKER_API
Contingent Worker
This package contains contingent worker APIs.
HR_CONTRACT_API
Employment Contract
This package contains APIs that maintain contract information for an
HR_DE_ASSIGNMENT_API
Assignment for Germany
This package contains assignment APIs for Germany.
HR_DE_EMPLOYEE_API
Employee for Germany
This package contains employee APIs for Germany.
HR_DE_LIABILITY_PREMIUMS_API
Liability Premium for Germany
This package contains APIs to maintain liability premiums for Germany.
HR_DE_ORGANIZATION_LINKS_API
Organization Link for Germany
This package contains APIs to maintain organization links for Germany.
HR_DE_SOC_INS_CLE_API
Social Insurance Contribution
This package contains APIs to maintain social insurnace contributions for
HR_DELIVERY_METHODS_API
Delivery Method
This package contains APIs that will maintain the delivery methods used to
HR_DEPLOYMENT_FACTOR_API
Deployment Factor
This package contains person deployment factor APIs.
HR_DK_APPLICANT_API
Applicant for Denmark
This package contains applicant APIs.
HR_DK_ASSIGNMENT_API
Assignment for Denmark
This package contains assignment APIs for Denmark.
HR_DK_CONTACT_API
Contact for Denmark
This package contains contact APIs.
HR_DK_CONTINGENT_WORKER_API
Contingent Worker for Denmark
This package contains contingent worker APIs.
HR_DK_EMPLOYEE_API
Employee For Denmark
This package contains employee APIs.
HR_DK_PERSON_API
Person for Denmark
This package contains person APIs.
HR_ELC_CANDIDATE_API
Election Candidate
This package creates candidates for an election for a representative body.
HR_ELC_CONSTITUENCYS_API
Election constituency
This API creates constituencies for election.
HR_ELC_RESULT_API
Election Result
This API updates the election candidate record with the election results and
HR_ELECTIONS_API
Election
This package contains APIs that create and maintain election information.
HR_EMPLOYEE_API
Employee
This package contains employee APIs.
HR_EMPLOYEE_APPLICANT_API
Employee Applicant
This package contains employee applicant APIs.
HR_ES_APPLICANT_API
Applicant for Spain
This package contains applicant APIs for Spain.
HR_ES_CONTACT_API
Personal Contact for Spain
This package contains contact APIs for Spain.
HR_ES_CONTINGENT_WORKER_API
Contingent Worker for Spain
This package contains contingent worker APIs for Spain.
HR_ES_EMPLOYEE_API
Employee for Spain
This package contains employee APIs for the Spain.
HR_ES_PERSON_API
Update Person for Spain
This package contains update person APIs for Spain.
HR_EX_EMPLOYEE_API
Ex-Employee
This package contains Ex-Employee APIs.
HR_FI_APPLICANT_API
Applicant for Finland
This package contains applicant APIs.
HR_FI_ASSIGNMENT_API
Assignment for Finland
This package contains assignment APIs for Finland.
HR_FI_CONTACT_API
Contact for Finland
This package contains contact APIs.
HR_FI_CONTINGENT_WORKER_API
Contingent Worker for Finland
This package contains contingent worker APIs.
HR_FI_EMPLOYEE_API
Employee for Finland
This package contains employee APIs for Finland.
HR_FI_PERSON_API
Person for Finland
This package contains person APIs for Finland.
HR_FI_PREVIOUS_EMPLOYMENT_API
Previous Employment for Finland
This package contains previous employment APIs for Finland.
HR_FI_QUALIFICATION_API
Qualification for Finland
This package contains qualification APIs for Finland.
HR_FR_APPLICANT_API
Applicant for France
This package contains an applicant API for France.
HR_FR_ASSIGNMENT_API
Assignment for France
This package contains Assignment APIs for France.
HR_FR_CONTRACT_API
Employment Contract for France
This package contains contract APIs for France.
HR_FR_EMPLOYEE_API
Employee for France
This package contains an API to maintain employees for France.
HR_FR_JOB_API
Job for France
This package contains a job API for France.
HR_FR_PERIODS_OF_SERVICE_API
Periods of Service for France
This package contains a period of service API for France.
HR_FR_PERS_PAY_METHOD_API
Personal Payment Method for France
This package contains personal payment method APIs for France.
HR_FR_PERSON_ADDRESS_API
Person Address for France
This package contains address APIs for France.
HR_FR_PERSON_API
Person for France
This package contains an API to maintain a person record for France.
HR_FR_PQH_EMPLOYEE_API
Employee for Public Sector France
This package contains APIs to create employee records in Public Sector
HR_GRADE_API
Grade
The package contains APIs that maintain grade information.
HR_GRADE_RATE_VALUE_API
Grade Rate Value
This package contains APIs that will maintain rate values for grades.
HR_GRADE_SCALE_API
Grade Scale
This package contains APIs that maintain grade scales.
HR_GRADE_STEP_API
Grade Step
This package contains APIs that maintain grade steps.
HR_HIERARCHY_ELEMENT_API
Organization Hierarchy Element
This package contains APIs that create and maintain hierarchy elements.
HR_HK_APPLICANT_API
Applicant for Hong Kong
This package contains applicant related APIs for Hong Kong.
HR_HK_ASSIGNMENT_API
Assignment for Hong Kong
This Package contains assignment related APIs.
HR_HK_EMPLOYEE_API
Employee for Hong Kong
This package contains employee APIs for Hong Kong.
HR_HK_PERSON_ADDRESS_API
Person Address for Hong Kong
This package contains person address related APIs.
HR_HK_PERSON_API
Person for Hong Kong
This package contains API for updation of Person details.
HR_HK_PERSONAL_PAY_METHOD_API
Personal Payment Method for Hong Kong
This package contains APIs for creation of personal payment methods for Hong
HR_HU_APPLICANT_API
Applicant for Hungary
This package contains Applicant APIs for Hungary.
HR_HU_CONTACT_API
Personal Contact for Hungary
This package contains contact APIs for Hungary.
HR_HU_CONTINGENT_WORKER_API
Contingent Worker for Hungary
This package contains Contingent Worker APIs for Hungary.
HR_HU_EMPLOYEE_API
Employee for Hungary
This package contains Employee APIs for Hungary.
HR_HU_PERSON_API
Update Person for Hungary
This package contains update person APIs for Hungary.
HR_IN_ASSIGNMENT_API
Assignment for India
This package contains the assignment APIs.
HR_IN_CONTACT_EXTRA_INFO_API
Contact Extra Information for India
This package contains contact extra information APIs.
HR_IN_CONTACT_REL_API
Contact Relationship for India
This package contains contact relationship APIs.
HR_IN_CONTINGENT_WORKER_API
Contingent Worker for India
This package contains contingent worker APIs.
HR_IN_EMPLOYEE_API
Employee for India
This package contains employee APIs.
HR_IN_LOCATION_API
Location for India
This package contains location APIs.
HR_IN_PERSON_ADDRESS_API
Person Address for India
This package contains person address APIs.
HR_IN_PERSON_API
Person for India
The package contains person APIs.
HR_IN_PERSON_EXTRA_INFO_API
Person Extra Information for India
This package contains person extra information APIs.
HR_IN_PERSONAL_PAY_METHOD_API
Personal Payment Method for India
The package contains personal payment method APIs.
HR_IT_EMPLOYEE_API
Employee for Italy
This package contains employee APIs for Italy.
HR_JOB_API
Job
This package contains APIs for maintaining job details.
HR_JOB_REQUIREMENT_API
Job Requirement
This package contains APIs for maintaining job requirement information.
HR_JP_PERSON_API
Person for Japan
This package contains the person APIs for Japan.
HR_JPBP_API
Business Process for Japan
This package contains business process APIs for Japan.
HR_KI_HIERARCHIES_API
Knowledge Integration Hierarchy
This package contains APIs that maintain the knowledge integration
HR_KI_INTEGRATIONS_API
Knowledge Integration
This package contains APIs that maintain HR Knowledge Integration
HR_KI_OPTION_TYPES_API
Knowledge Integration Option Type
This package contains APIs to maintain knowledge integration option types.
HR_KI_OPTIONS_API
Knowledge Integration Option
This package contains APIs to maintain the knowledge integration privilege
HR_KI_TOPIC_INTEGRATIONS_API
Knowledge Integration – Topic Integration
This package contains APIs that maintain definitions of the knowledge
HR_KI_TOPICS_API
Knowledge Integration -Topic
This API maintains definitions of the knowledge integration topics used
HR_KI_USER_INTERFACES_API
Knowledge Integration User Interface
This package contains APIs that maintain definition for the HR Knowledge
HR_KW_APPLICANT_API
Applicant for Kuwait
This package contains applicant APIs.
HR_KW_CONTACT_API
Contact for Kuwait
This package contains contact APIs.
HR_KW_CONTINGENT_WORKER_API
Contingent Worker for Kuwait
This package contains contingent worker APIs.
HR_KW_CONTRACT_API
Contract for Kuwait
This package contains contract APIs for Kuwait.
HR_KW_EMPLOYEE_API
Employee for Kuwait
This package contains employee APIs for Kuwait.
HR_KW_PERSON_API
Person for Kuwait
This package contains Person APIs for Kuwait.
HR_KW_PREVIOUS_EMPLOYMENT_API
Previous Employment for Kuwait
This package contains previous employment APIs.
HR_LOCATION_API
Location
This package contains APIs for maintaining location information.
HR_LOCATION_EXTRA_INFO_API
Location Extra Information
This package contains APIs to maintain location extra information records.
HR_MAINTAIN_PROPOSAL_API
Maintain Proposal
This package contains APIs for creating and maintaining Salary Proposal
HR_MX_APPLICANT_API
Applicant for Mexico
This package contains applicant APIs for Mexico.
HR_MX_ASSIGNMENT_API
Assignment for Mexico
This package contains APIs for maintaining employee, applicant and
HR_MX_CONTACT_API
Personal Contact for Mexico
This API creates a new contact for Mexico.
HR_MX_CONTACT_REL_API
Contact Relationship for Mexico
This package contains APIs to maintain contact relationship information for
HR_MX_CONTINGENT_WORKER_API
Contingent Worker for Mexico
This API creates contingent worker for Mexico.
HR_MX_EMPLOYEE_API
Employee for Mexico
This package contains employee APIs for Mexico.
HR_MX_EMPLOYEE_APPLICANT_API
Employee Applicant for Mexico
This package contains employee applicant APIs for Mexico.
HR_MX_EX_EMPLOYEE_API
Ex-Employee for Mexico
This package contains Ex-Employee APIs for Mexico.
HR_MX_PERSON_ABSENCE_API
Person Absence for Mexico
This package contains Person Absence APIs.
HR_MX_PERSON_API
Person for Mexico
This API updates person information for Mexico.
HR_MX_PERSONAL_PAY_METHOD_API
Personal Payment Method for Mexico
This package contains personal payment method APIs for Mexico
HR_NL_ABSENCE_ACTION_API
Absence Action for Netherlands
This package contains Dutch Absence Action APIs.
HR_NL_ASSIGNMENT_API
Assignment for Netherlands
This package contains assignment APIs for the Netherlands.
HR_NL_EMPLOYEE_API
Employee for Netherlands
This package contains employee APIs for the Netherlands.
HR_NO_APPLICANT_API
Applicant for Norway
This package contains applicant APIs.
HR_NO_ASSIGNMENT_API
Assignment for Norway
This package contains the assignment APIs for Norway.
HR_NO_CONTACT_API
Contact for Norway
This package contains the contact APIs for Norway.
HR_NO_CONTINGENT_WORKER_API
Contingent Worker for Norway
This package contains contingent worker APIs.
HR_NO_CONTRACT_API
Contract for Norway
This package contains contract APIs for Norway.
HR_NO_EMPLOYEE_API
Employee for Norway
This package contains employee APIs for Norway.
HR_NO_JOB_API
Create Job for Norway
This package contains job APIs for Norway.
HR_NO_PERSON_API
Person APIs for Norway
This package contains person APIs for Norway.
HR_NO_QUALIFICATION_API
Qualification APIs for Norway.
This package contains Qualification APIs for Norway.
HR_NO_QUALIFICATION_TYPE_API
Qualification Type for Norway
This package contains qualification type APIs for Norway.
HR_NZ_APPLICANT_API
Applicant for New Zealand
This package contains applicant related APIs.
HR_NZ_ASSIGNMENT_API
Assignment for New Zealand
This package contains assignment related APIs.
HR_NZ_EMPLOYEE_API
Employee for New Zealand
This package contains employee APIs for New Zealand.
HR_NZ_PERSON_ADDRESS_API
Person Address for New Zealand
This package contains person address related APIs.
HR_NZ_PERSON_API
Person for New Zealand
This package contains API for updation of Person details.
HR_NZ_PERSONAL_PAY_METHOD_API
Personal Payment Method for New Zealand
This Package contains APIs for creation of personal payment methods for New
HR_NZ_TAX_API
Tax for New Zealand
This package contains tax related APIs.
HR_OBJECTIVES_API
Objective
This package contains objective APIs.
HR_ORGANIZATION_API
Organization
This package contains APIs that create and manage HR Organizations.
HR_PARTICIPANTS_API
Participant
This package contains participant APIs.
HR_PAY_SCALE_API
Scale
This package contains APIs that maintain pay scales.
HR_PAY_SCALE_VALUE_API
Scale Rate Value
This package contains APIs that will maintain rate values for pay scale
HR_PERF_REVIEW_API
Performance Review
This package contains performance review APIs.
HR_PERFORMANCE_RATINGS_API
Performance Rating
This package contains performance ratings APIs.
HR_PERIODS_OF_PLACEMENT_API
Period of Placement
This package contains Period of Placement APIs.
HR_PERIODS_OF_SERVICE_API
Periods of Service
This package contains Period of Service APIs.
HR_PERSON_ABSENCE_API
Person Absence
This package contains APIs to create update and delete absences for a
HR_PERSON_ADDRESS_API
Person Address
This package contains APIs that create and maintain address information for
HR_PERSON_API
Person
This API updates person information.
HR_PERSON_EXTRA_INFO_API
Person Extra Information
This API maintains person extra information.
HR_PERSON_TYPE_USAGE_API
Person Type Usage
This package maintains person type usages.
HR_PERSONAL_PAY_METHOD_API
Personal Payment Method
This package contains Personal Payment Method APIs.
HR_PHONE_API
Phone
This package contains phone APIs.
HR_PL_APPLICANT_API
Applicant for Poland
This package contains applicant APIs.
HR_PL_ASSIGNMENT_API
Assignment for Poland
This package contains assignment APIs.
HR_PL_CONTACT_API
Contact for Poland
This package contains contact APIs.
HR_PL_CONTACT_REL_API
Contact Relationship for Poland
This package contains contact relationship APIs.
HR_PL_CONTINGENT_WORKER_API
Contingent Worker for Poland
This package contains contingent APIs.
HR_PL_EMPLOYEE_API
Employee for Poland
This package contains employee APIs.
HR_PL_PERSON_ADDRESS_API
Personal Address for Poland
This package contains person address APIs.
HR_PL_PERSON_API
Person for Poland
This package contains person APIs.
HR_PL_PERSONAL_PAY_METHOD_API
Personal Payment Method for Poland
This package contains personal payment method APIs.
HR_PL_PREVIOUS_EMPLOYMENT_API
Previous Employment for Poland
This package contains previous employment APIs.
HR_POS_HIERARCHY_ELE_API
Position Hierarchy Element
This package contains APIs that create and maintain position hierarchy
HR_POSITION_API
Position
This package contains position APIs.
HR_POSITION_EXTRA_INFO_API
Position Extra Information
This package contains APIs that create and maintain position extra
HR_POSITION_REQUIREMENT_API
Position Requirement
This package contains position requirement API.
HR_PREVIOUS_EMPLOYMENT_API
Previous Employment
This package contains APIs that maintain previous employment information for
HR_PROGRESSION_POINT_API
Scale Point
This package contains APIs that maintain points within pay scales.
HR_QUALIFICATION_TYPE_API
Qualification Type
This package contains qualification type APIs.
HR_RATE_API
Rate
This package contains HR Rate APIs.
HR_RATE_VALUES_API
Rate Value
This package contains APIs to create and maintain Grade Rate Values and
HR_RATING_LEVELS_API
Rating Level
This package contains rating level APIs.
HR_RATING_SCALES_API
Rating Scale
This package contains rating scale APIs.
HR_RETAINED_RIGHTS_API
Retained Rights
This package contains APIs which create and maintain retained rights.
HR_RU_APPLICANT_API
Applicant for Russia
This package contains applicant APIs for Russia.
HR_RU_CONTACT_API
HR contact APIs for Russia
This package contains contact APIs for Russia.
HR_RU_CONTINGENT_WORKER_API
Contingent Worker for Russia
This package contains contingent worker APIs for Russia.
HR_RU_EMPLOYEE_API
Employee for Russia
This package contains employee APIs for Russia.
HR_RU_PERSON_API
Update Person for Russia
This package contains update person APIs for Russia.
HR_SA_APPLICANT_API
Applicant for Saudi Arabia
This package contains applicant APIs for Saudi Arabia.
HR_SA_ASSIGNMENT_API
Assignment for Saudi Arabia
This package contains assignment APIs for Saudi Arabia.
HR_SA_CONTACT_API
Personal Contact for Saudi Arabia
This package contains contact APIs for Saudi Arabia.
HR_SA_CONTINGENT_WORKER_API
Contingent Worker for Saudi Arabia
This package contains contingent worker APIs for Saudi Arabia.
HR_SA_CONTRACT_API
Employment Contract for Saudi Arabia
This package contains contract APIs for Saudi Arabia.
HR_SA_EMPLOYEE_API
Employee for Saudi Arabia
This package contains employee APIs for Saudi Arabia.
HR_SA_PERIODS_OF_SERVICE_API
Period of Service for Saudi Arabia
This package contains period of service APIs for Saudi Arabia.
HR_SA_PERSON_API
Person for Saudi Arabia
This package contains person APIs for Saudi Arabia.
HR_SA_PREVIOUS_EMPLOYMENT_API
Previous Employment for Saudi Arabia
This package contains previous employment APIs for Saudi Arabia.
HR_SALARY_BASIS_API
Salary Basis
This package contains APIs to create and maintain a salary basis.
HR_SALARY_SURVEY_API
Salary Survey
This package contains APIs to create and maintain salary survey master
HR_SALARY_SURVEY_LINE_API
Salary Survey Line
This package contains APIs to create and maintain a Salary Survey details
HR_SALARY_SURVEY_MAPPING_API
Salary Survey Mapping
This package contains APIs to create and manage mappings of salary survey
HR_SG_APPLICANT_API
Applicant for Singapore
This package contains Applicant related APIs for Singapore.
HR_SG_ASSIGNMENT_API
Assignment for Singapore
This package contains assignment related APIs.
HR_SG_EMPLOYEE_API
Employee for Singapore
This API creates employes for Singapore.
HR_SG_PERSON_API
Person for Singapore
This API updates the Person details.
HR_SG_PERSONAL_PAY_METHOD_API
Personal Payment Method for Singapore
This Package contains APIs for creation of personal payment methods for
HR_SIT_API
Special Information Type
This package contains APIs which create and maintain special information
HR_SP_PLACEMENT_API
Grade Step Placement
This package contains APIs that create and maintain the grade step
HR_STD_HOL_ABS_API
Standard Holiday Absence
This package contains Standard Holiday Absence APIs.
HR_UPLOAD_PROPOSAL_API
Upload Proposal API
This package contains APIs to create and maintain salary proposals.
HR_VALID_GRADE_API
Valid Grade
This package contains APIs that create and maintain valid grades.
HXC_RECURRING_PERIODS_API
Recurring Period
This package contains Recurring Periods APIs.
HXC_TIME_RECIPIENT_API
Time Recipient
This package contains Time Recipient APIs.
HXC_TIME_SOURCE_API
Time Source
This package contains Time Source APIs.
HXC_TIMESTORE_DEPOSIT
TimeStore Deposit
This package contains procedures that can be used to manage timecards and
HZ_CLASSIFICATION_V2PUB
Classification
This package contains the public APIs for class categories, class code
HZ_CONTACT_POINT_V2PUB
Contact Point
This package contains the public APIs for contact points.
HZ_CONTACT_PREFERENCE_V2PUB
Contact Preference
This package contains the public APIs for contact preference.
HZ_CUST_ACCOUNT_ROLE_V2PUB
Customer Account Role
This package contains the public APIs for customer account roles and role
HZ_CUST_ACCOUNT_SITE_V2PUB
Customer Account Site
This package contains the public APIs for customer account sites and site uses.
HZ_CUST_ACCOUNT_V2PUB
Customer Account
This package contains the public APIs for customer accounts and related entities.
HZ_CUSTOMER_PROFILE_V2PUB
Customer Profile
This package contains the public APIs for customer profiles and customer profile
HZ_FORMAT_PHONE_V2PUB
Format Phone
This package contains the public APIs used to parse the phone number into country_code,
HZ_FORMAT_PUB
Name and Address Formatting
This package contains the public APIs for Global Name and Address formatting.
HZ_HIERARCHY_V2PUB
Hierarchy Retrieval
This package includes the public APIs for hierarchy retrieval.
HZ_LOCATION_SERVICES_PUB
Location Service
This package contains the public APIs for submitting address validation requests.
HZ_LOCATION_V2PUB
Location
This package contains the public APIs for locations.
HZ_ORGANIZATION_INFO_V2PUB
Organization Information
This package contains the public APIs for Organization-related entities.
HZ_ORIG_SYSTEM_REF_PUB
Source System Management
This package contains the public APIs related to source systems.
HZ_PARTY_CONTACT_V2PUB
Party Contact
This package includes the create and update procedures for contacts and contact roles.
HZ_PARTY_INFO_V2PUB
Credit Request Creation
The Credit Request Creation API creates a credit request in the Credit
HZ_PARTY_SITE_V2PUB
Party Site
This package includes the create and update procedures for all party site information.
HZ_PARTY_V2PUB
Party
This package contains the public APIs to create and update persons, organizations, and groups.
HZ_PERSON_INFO_V2PUB
Person Information
This package contains the public APIs for person-related entities.
HZ_RELATIONSHIP_TYPE_V2PUB
Relationship Type
This package contains the public APIs for relationship types.
HZ_RELATIONSHIP_V2PUB
Party Relationship
This package contains the public APIs for party relationships.
IBY_INSTRREG_PUB
Payment Instrument Registration
The IBY_INSTRREG_PUB is the public interface for payment instrument
IBY_PAYMENT_ADAPTER_PUB
Credit Card Payment Processing
The IBY_PAYMENT_ADAPTER_PUB package provides payment processing APIs.
IEX_CASE_UTL_PUB
Create Update Collections Case
Start of Comments
IEX_DISPUTE_PUB
Create Dispute
Creates a dispute.
IEX_PROMISES_PUB
IEX Promises API
Creates a promise.
IEX_SCORE_PUB
Scoring API
Scoring APIs allow the user to manage scoring engines in Oracle Collections.
IEX_STRATEGY_PUB
Create/Close Collections Strategy
Start of Comments
IGC_CC_OPN_UPD_GET_LNK_PUB
Contract Commitment Operations API
This package body is used for Contract Commitment Open API operations.
IGF_SE_PAYMENT_PUB
Create payment
A public API that creates payment information for a given authorization id
IGF_SP_ASSIGN_PUB
Import Sponsorship Relationships
The Sponsorship Assignment API is a public API that is used to assign a number of students to a sponsor automatically.
IGS_ADMAPPLICATION_PUB
Admission Application
This Package contains Public API's for giving ratings,Outcome,offer reponse and Qualificationcodes to an Admission Application
IGS_AS_SUAO_LGCY_PUB
Import Legacy Unit Outcome
The Unit Outcome Legacy import process is a public API designed for use in populating rows with data during a system conversion.
IGS_AS_SUARC_LGCY_PUB
Import Legacy Student Unit Attempt Reference Codes
The Unit Attempt Reference Codes Legacy import process is a public API designed for use in populating rows with data during a system conversion.
IGS_AS_TRNCMT_LGCY_PUB
Import Legacy Transcript Comment
The Transcript Comments import process is a public API designed for use in populating rows with data during a system conversion.
IGS_AV_LVL_LGCY_PUB
Import Legacy Level Advanced Standing
The Advanced Standing Unit Level Legacy Import process is a public API designed for use in populating rows with data during a system conversion.
IGS_AV_UNT_LGCY_PUB
Import Legacy Unit Advanced Standing
The Advanced Standing Unit Legacy Import process is a public API designed for use in populating rows with data during a system conversion.
IGS_EN_SPA_LGCY_PUB
Import Legacy Student Program Attempt
The Student Program Attempt Legacy Import process is a public API designed for
IGS_EN_SPAT_LGCY_PUB
Import Legacy Student Term Record
The Student Program Attempt Term Record Import process is a public API designed for use in populating
IGS_EN_SPI_LGCY_PUB
Import Legacy Intermission
The Student Intermission Import process is a public API designed for use in populating rows with
IGS_EN_SPAA_LGCY_PUB
Import Legacy Award Aim
The Student Award Aim Import process is a public API designed for use in populating rows with data
IGS_EN_SUA_LGCY_PUB
Import Legacy Student Unit Attempt
The Student Unit Attempt Import process is a public API designed for use in populating rows with
IGS_EN_SUSA_LGCY_PUB
Import Legacy Student Unit Set Attempt
The Student Unit Set Attempt Import process is a public API designed for use in populating rows
IGS_FI_CREDITS_API_PUB
Import Credits
The Credits API is a public API that is used internally or externally to create payment or credit information in the Student Finance module of Student System.
IGS_FI_WAIVERS_API_PUB
Import Manual Waivers
The Import Waives API is a public API used externally to create waivers.
IGS_GR_GRD_LGCY_PUB
Import Legacy Graduand
The Graduand Legacy import process is a public API designed for use in populating rows with data during a system conversion.
IGS_HE_SPA_LGCY_PUB
Import Legacy Student Program Attempt HESA Detail
The Student Program Attempt HESA Detail Import process is a public API designed for use in populating rows with data during a system conversion.
IGS_HE_SUSA_LGCY_PUB
Import Legacy Student Unit Set Attempt HESA Detail
The Student Unit Set Attempt HESA Detail Import process is a public API designed for use in populating rows with data during a system conversion.
IGS_PR_CLSRNK_LGCY_PUB
Import Legacy Class Rank
The Class Rank Legacy import process is a public API designed for use in populating rows with data during a system conversion.
IGS_PR_PROUT_LGCY_PUB
Import Legacy Progression Outcome
The Progression Outcome Legacy import process is a public API designed for use in populating rows with data during a system conversion.
IGS_PRECREATE_APPL_PUB
Pre-Create Admission Application
The Pre Create Application Package contains Public API's for Pre Creation of Admission Application.
IGS_PS_GENERIC_PUB
Program Structure and Planning Import
A public API to import data from external system to OSS for unit section and its details.
IGS_PS_SCH_INT_API_PUB
Import Schedule
A public API to import data into Scheduling Occurrence Interface Table.
IGS_RATINGS_PUB
Assign Review Groups and Evaluators
This Package contains Public API's for assigning review groups and Evaluators to Admission Application.
IGS_RE_SPRVSR_LGCY_PUB
Import Legacy Research Supervisor
The Research Supervisor Import process is a public API designed for use in populating rows with
IGS_RE_THE_LGCY_PUB
Import Legacy Research Thesis
The Research Thesis Import process is a public API designed for use in populating rows with data
INV_ITEM_CATEGORY_PUB
Category Maintainence
This package provides functionality for maintaining categories, category
INV_KANBAN_PUB
Kanban Replenishment
This package provides routines for performing replenishment through
INV_LABEL
Label Printing request for WMS/MSCA
This procedure initiates a label print request for Oracle Warehouse
INV_LOC_WMS_PUB
Locator Maintenance API
The Locator Maintenance procedures allow users to create, update and delete
INV_LOT_API_PUB
Lot Number API
This is the public API for Inventory lots allows a user to create, update and
INV_MGD_MVT_STATS_PUB
Movement Statistics
Provides various functionality for the Movement Statistics module.
INV_MOVE_ORDER_PUB
Move Order API
Use these procedures to create and process Move Order Headers and Lines
INV_PICK_RELEASE_PUB
Inter-company transaction flow
This package provides APIs used to Pick Release
INV_QUANTITY_TREE_PUB
Quantity Tree
This package provides routines to query material availability for
INV_RESERVATION_PUB
Material Reservation
This is the public package that is used to create, update, delete, and
INV_RESERVATIONS_INTERFACE
Material Reservation Interface
This package contains programs to process reservation
INV_SALESORDER
Inventory Sales Orders
This package can be used to manage sales orders that are represented in
INV_SERIAL_NUMBER_PUB
Inventory Serial Number
The Serial Numbers procedures allow users to create, update and validate serials.
INV_TRANSACTION_FLOW_PUB
Inter-company transaction flow
This package provides routines to create, update and query
INV_TXN_MANAGER_PUB
Material Transaction
This package contains the Inventory Transactions Process wrapper which
IRC_AGENCY_VACANCIES_API
Agency Vacancy
This package contains agency vacancy APIs.
IRC_ASG_STATUS_API
Assignment Status History
This package contains Assignment Status History APIs.
IRC_DEFAULT_POSTING_API
Default Posting
This package contains Default Posting APIs.
IRC_DOCUMENT_API
Document
This package contains Document APIs.
IRC_JOB_BASKET_ITEMS_API
Job Basket
This package contains Job Basket APIs.
IRC_NOTIFICATION_PREFS_API
Notification Preferences
This package contains Notification Preferences APIs.
IRC_PARTY_API
Party
This package contains Recruiting Candidate APIs.
IRC_POSTING_CONTENT_API
Posting Content
This package contains APIs for job adverts.
IRC_REC_TEAM_MEMBERS_API
Recruiting Team Member
This package contains APIs for recruiting team members.
IRC_RECRUITING_SITES_API
Recruiting Site
This package contains APIs for recruiting sites.
IRC_SEARCH_CRITERIA_API
Search Criteria
This package contains APIs for work preferences, job searches and vacancy
IRC_VACANCY_CONSIDERATIONS_API
Vacancy Consideration
This package contains APIs for marking the consideration status of a
IRC_VARIABLE_COMP_ELEMENT_API
Variable Compensation Element
This package contains APIs for variable compensation elements for a vacancy.
JTF_IH_PUB
Customer Interaction Management
The JTF_IH_PUB package provides a common framework for CRM modules to
JTF_NOTES_PUB
Notes
This is a Notes public interface that can be used to validate, create, update, and delete notes.
JTF_RS_GROUPS_PUB
Groups API
Group create and update API
JTF_RS_RESOURCE_PUB
Resource API
Resource create/update/delete API
JTF_RS_SALESREPS_PUB
Salespersons API
Salesperson create and update API
JTF_TASK_ASSIGNMENTS_PUB
Task Assignment
This is a Task Manager public interface that can be used to validate, create, update, and delete task assignments.
JTF_TASK_REFERENCES_PUB
Task Reference
This is a Task Manager public interface that can be used to validate, create, update, and delete task references.
JTF_TASKS_PUB
Task Manager
This is a Task Manager public interface that can be used to validate, create, update, and delete tasks.
JTF_TERR_ASSIGN_PUB
Get Winning Territory-Resources
This package provides a public API to find the winning territory-resources
JTF_TERRITORY_PUB
Create Territory and Assign Resources
This package provides the public APIs for creating a territory or
JTF_TTY_GEOSOURCE_PUB
Create Geography Data
This package provides a public API for inserting geographic
MTL_CCEOI_ACTION_PUB
Cycle Count Interface API
The Cycle Count Interface procedures allow users to perform online processing to the Cycle Count
MYPACKAGENAME
Billing Extensions
This extension contains procedure that will be called by the Oracle Projects for Billing Extensions.
OE_INBOUND_INT
Sales Order Services
This API allows clients to perform various operations on sales orders.
OE_ORDER_PUB
Process Order API
This public API allows users to perform various operations on sales orders in the Order Management system.
OKE_IMPORT_CONTRACT_PUB
Create Project Contract
This is the public interface to import project contracts.
OKL_ACCOUNT_GENERATOR_PUB
Account Generator API
Account generator derives the account code based on the
OKL_ACCOUNTING_PROCESS_PUB
Accounting Process API
Accounting Process transfers accounting entries from the accounting
OKL_AM_AMORTIZE_PUB
Amortize API
Amortize API creates and updates asset amortization transactions.
OKL_AM_CREATE_QUOTE_PUB
Create Termination Quote API
Create Termination Quote API allows users to create a termination quote
OKL_AM_INTEGRATION_PUB
Termination API
Terminate API invalidates all termination quotes that
OKL_AM_LEASE_LOAN_TRMNT_PUB
Termination API
Terminate API terminates the lease or loan contract.
OKL_AM_TERMNT_QUOTE_PUB
Termination Quote API
Terminate Quote API allows users to create, update and approve termination
OKL_CASH_APPL_RULES_PUB
Cash Application
Cash Application Rules API allows users to create a receipt against
OKL_CONTRACT_BOOK_PUB
Contract Booking API
Contract Booking API allows users to book a lease contract.
OKL_CONTRACT_STATUS_PUB
Get Contract Status API
Contract Status API allows users to get the status of a
OKL_CREATE_ADJST_PUB
Lease Adjustments
Create Adjustment API allows users to adjust an invoice balance in
OKL_CREDIT_MEMO_PUB
Credit Memo
Credit Memo API creates credit memos in the transaction tables.
OKL_CREDIT_PUB
Credit API
Credit API allows users to perform actions on credit lines
OKL_EXECUTE_FORMULA_PUB
Execute Formula API
Execute Formula API validates and executes the formula using the parameters
OKL_INS_POLICIES_PUB
Insurance Policy API
Insurance Policy API allows users to perform actions on
OKL_INS_QUOTE_PUB
Insurance Quote
Insurance Quote API creates insurance quotes and policies for a contract.
OKL_INTEREST_CALC_PUB
Interest Calculation
Interest Calculation API calculates interest on amounts paid
OKL_LTE_PLCY_WRAP_PUB
Late Policies API
Create Late Policies API allows users to perform actions
OKL_MASTER_LEASE_AGREEMENT_PUB
Master Lease Agreement API
Master Lease Agreement API allows users to perform actions on
OKL_RULE_APIS_PUB
Get Contract Rules API
Contract Rules API allows users to obtain the terms and
OKL_RULE_EXTRACT_PUB
Contract Rules API
Contract Rules API allows users to query terms and conditions
OKL_SETUPDQUALITYS_PUB
Create Quality Association API
Setup Product Quality Association API allows users to perform
OKL_SETUPPDTTEMPLATES_PUB
Setup Product Templates API
Setup Product Templates API allows users to perform actions
OKL_SETUPPQUALITYS_PUB
Setup Product Qualities API
Setup Product Qualities API allows users to perform actions on
OKL_SETUPPQVALUES_PUB
Setup Product Quality Values API
Setup Product Quality Values API allows users to perform actions
OKL_SETUPPQYVALUES_PUB
Setup Quality Values API
Setup Quality Values API allows users to perform actions on
OKL_SETUPPRODUCTS_PUB
Create Product API
Setup Product API allows users to perform actions
OKL_STREAM_BILLING_PUB
Stream Billing API
Stream Billing API extracts eligible stream elements and
OKL_VENDOR_AGREEMENT_PUB
Vendor Agreement API
Vendor Agreement API allows users to perform actions on
OKS_CON_COVERAGE_PUB
Coverage utility procedures
Package containing APIs to retrieve Coverage information, specifically,
OKS_ENTITLEMENTS_PUB
OKS Entitlements
Package of procedures and functions for retrieving contract information and
OKS_OMINT_PUB
Order Integration utility procedures
Package of APIs for retrieving customer Service information, specifically,
OTA_ACTIVITY_CATEGORY_API
Course Category
This package contains the Course Category API.
OTA_ACTIVITY_VERSION_API
Course
This package contains the course APIs.
OTA_ANNOUNCEMENT_API
Announcement
This package contains the Announcement APIs.
OTA_CATEGORY_USAGE_API
Category Usage
This package contains the category usage APIs that create or update a
OTA_EVENT_API
Class
This package contains the class APIs.
OTA_LEARNING_PATH_API
Learning Path
This package contains the Learning Path APIs.
OTA_LP_CATEGORY_API
Learning Path Category Inclusion
This package contains Learning Path Category Inclusion APIs.
OTA_LP_ENROLLMENT_API
Learning Path Enrollment
This package contains learning path enrollment APIs.
OTA_LP_MEMBER_API
Learning Path Component
This package contains Learning Path Component APIs.
OTA_LP_MEMBER_ENROLLMENT_API
Learning Path Component Enrollment
This package contains learning path component enrollment APIs.
OTA_LP_SECTION_API
Learning Path Section
This package contains learning path section APIs.
OTA_NHS_API
External Learning
This package creates and updates a user's external learning.
OTA_OFFERING_API
Offering
This package contains the offering APIs.
OTA_RESOURCE_BOOKING_API
Resource Booking
This package contains the Resource Booking APIs.
OTA_RESOURCE_USAGE_API
Resource Usage
This package creates, updates, and deletes resource associations at the
OTA_SKILL_PROVISION_API
Course Other Information
This package contains the Course Other Information APIs.
OTA_TCC_API
Cross Charge
This package contains the APIs to create and update a cross charge.
OTA_TDB_API_INS2
Enrollment
This package creates a learner enrollment in a class.
OTA_TDB_API_UPD2
Enrollment
This package updates the enrollment of a learner in a class.
OTA_THG_API
General Ledger Flexfield
This API maintains the detail mappings between Oracle General Ledger's Chart
OTA_TMT_API
Measurement Type
This package contains the measurement type APIs for use by Organization
OTA_TPC_API
Training Plan Cost
This package contains the Organization Training Plan Cost APIs.
OTA_TPM_API
Training Plan Component
The APIs in this package create, update, and delete Personal and
OTA_TPS_API
Training Plan
The APIs in this package create, update, and delete personal or organization
OZF_CHARGEBACK_ATTRMAP_PUB
Chargeback Attribute Mapping API
This package defines the procedures that repopulate the Global Header structure
OZF_CLAIM_PUB
Claim Public API
This is the public API for claims, and this package includes procedures
OZF_FUNDS_PUB
Budget Public API
This package is used to create, update, and delete funds in Oracle Trade
OZF_OFFER_PUB
Offer Public API
The Offers Public API is used for creating and updating offers from
OZF_RESALE_PUB
Indirect Sales Data Processing Public API
This package defines the procedures that are required for processing
PA_AGREEMENT_PUB
Create Agreement
This package contains the public AMG APIs for agreement and
PA_BUDGET_PUB
Create Budget
This package contains the public AMG APIs for Budgets that will be used by the external system
PA_CALC_OVERTIME
Overtime Calculation.
This is a template package that contains the procedures that can be modified to implement overtime calculation.
PA_CC_AP_INV_CLIENT_EXTN
internal Payables invoice Attributes Override Extension.
When using Intercompany or Inter Project Billing, you must define organization controls using the Provider/Receiver Controls window.
PA_CC_CA
Cost Accrual Identification
This extension is used for cost accrual identification.
PA_CC_IDENT_CLIENT_EXTN
Cross Charge Processing Method Override
This package contains the extensions you can use to implement your business rules for various aspects of cross charge feature.
PA_CC_TP_CLIENT_EXTN
Transfer Price Override.
The extension determine_transfer_price specifies a transfer price for the transaction being processed.
PA_CI_NUMBER_CLIENT_EXTN
Control Item Document Numbering Extension
This extension enables you to create your own logic for numbering issues and change documents when automatic numbering is enabled
PA_CLIENT_EXTEN_CIP_GROUPING
CIP Grouping
This extension is used to define a unique method that your company uses to specify how expenditure lines are grouped to form asset lines.
PA_CLIENT_EXTN_ALLOC
Create Client Extension Allocation
This extension contains procedures that define the source,target,offset,basis for an allocation rule,
PA_CLIENT_EXTN_ASGMT_APPRVL
Assignment Approval Changes Extension
This extension enforces change in duration,change in work type conditions whether an approval is required for an assignment.
PA_CLIENT_EXTN_ASSET_ALLOC
Asset Allocation Basis Extension
This extension is used to define your own allocation bases for allocating unassignedand common costs across multiple project assets.
PA_CLIENT_EXTN_ASSET_CREATION
Asset Lines Processing Extension
This extension is used to create project assets(capital assets and retirement adjustment assets) and asset assignment
PA_CLIENT_EXTN_BILL_CYCLE
Client Extension Billing Cycle
This extension contains a function to derive the next billing date for a project.
PA_CLIENT_EXTN_BILLING
Labor Billing.
This Package is used as the basis of your labor billing extension procedures.
PA_CLIENT_EXTN_BUDGET
Budget Calculation
This extension allows you to define rules for validating a budget before it's status is changed.
PA_CLIENT_EXTN_BUDGET_WF
Budget Workflow
This extension enables you to customize the workflow processes for changing the status of a budget.
PA_CLIENT_EXTN_BURDEN
Burden Costing
This extension is used to override the burden schedule ID.
PA_CLIENT_EXTN_CAP_INT
Capitalized Interest Extension
This extension enables you to customize the capitalized interest calculation process.
PA_CLIENT_EXTN_CHECK_CMT
Commitment Changes
When you run the PRC:Update Project Summary Amounts process,Oracle Projects checks the commitments for each project to see
PA_CLIENT_EXTN_CIP_ACCT_OVR
CIP Account Override Extension
This extension enables you to override the CIP account associated with the asset lineto specify a different account for
PA_CLIENT_EXTN_COSTING
Labor Costing.
This is a Labor Costing Extension Package Specification template.
PA_CLIENT_EXTN_DEPRN_EXP_OVR
Depreciation Account Override Extension
This package contains the function which is defined for Depreciation Expense Account Override.
PA_CLIENT_EXTN_DFFTRANS
Client Extension Descriptive Flexfield Transfer
This extension is used to map segments of descriptive flexfield that are transferred from Payables to
PA_CLIENT_EXTN_FUNDING_REVAL
Funding Revaluation Factor.
This Client Extension can be use customized by the users to specify funding revalution factor to revise the founding amount.
PA_CLIENT_EXTN_GEN_ASSET_LINES
Asset Assignment
This extension contains procedures used to generate asset lines process.Oracle Projects callsthis extension.
PA_CLIENT_EXTN_INV_ACTIONS
Automatic Invoice Approve/Release
This extension contains procedures that you use as the basis of automatic invoiceapprove/
PA_CLIENT_EXTN_INV_TRANSFER
AR Transcation Type Extension
This extension is used for AR Transcation type billing.
PA_CLIENT_EXTN_OUTPUT_TAX
Output Tax
In the Tax Defaults implementation option, you set up a hierarchy for determining default tax codes for invoice lines.
PA_CLIENT_EXTN_PRE_CAP_EVENT
Capital Event Processing Extension
This extension is called by PRC:Create Periodic Capital Event process for eachproject for which a capital event is created.
PA_CLIENT_EXTN_PROJ_STATUS
Project Verification.
This extension you use as the basis of your project verification.
PA_CLIENT_EXTN_PROJECT_WF
Project Workflow.
This extension is used as the basis of your project workflow extension.
PA_CLIENT_EXTN_PTE
AutoApproval
This extension contains procedures to define consitions under which expense reports are approved automatically.
PA_CLIENT_EXTN_RETENTION
Retention Billing.
This Client Extension can be customized by the users to define company's business rules to bill withheld amounts.
PA_CLIENT_EXTN_STATUS
Project Status inquiry(PSI)
You can use a PSI client extension to derive an alternate column value, even if you have entered a column definition in the PSI Columns
PA_CLIENT_EXTN_TXN
Labor Transaction.
Oracle Projects provides a template package and procedure that you use as the basis of your labor transaction extension procedures.
PA_CONTROL_ITEMS_WF_CLIENT
issue and Change Workflow.
This extension enables you to customize the workflow processes for
PA_COST_PLUS
Cost Plus Applications Programming Interface (API)
Oracle Projects provides a procedure you can use to call the Cost Plus Application Programming Interface.
PA_EVENT_PUB
Project Billing Events
This package contains the public AMG APIs which provide an open interface for external systems
PA_ORG_CLIENT_EXTN
Verify Organization Change.
The Verify Organization Change Extension enables you to build business rules to determine whether an organization change is allowed
PA_OVERRIDE_AR_INST
Receivables installation Override.
The Receivables Installation Override client extension allows you to use a third-party receivables system for the majority of your receivables
PA_PROJECT_PUB
Project Pub
This package contains the public AMG APIs for project and task information
PA_PURGE_EXTN
Archive Custom Tables
This package contains the extensions to purge your custom tables.By default the extension
PA_PURGE_EXTN_VALIDATE
Archive Project Validation
This package contains the extension to define additional business rules for validating projects.
PA_REPORT_WORKFLOW_CLIENT
Project Status Report Workflow.
This is a project status report workflow extension which enables you to customize
PA_RESOURCE_PUB
Create Resources
This package contains the public AMG APIs that will be used to export the resource lists and the resources
PA_REV_CA
Cost Accrual[Billing]
You can use the cost accrual billing extension client extension to apply
PA_SECURITY_EXTN
Project Security.
This extension used for implementing Project security.
PA_TRX_IMPORT
Post-import{Transaction import}.
Oracle Projects provides a single open interface, called Transaction Import.
PA_WORKPLAN_WORKFLOW_CLIENT
Workplan Workflow.
The workplan workflow extension enables you to customize the workflow processes for submitting,
PATCX
Transaction Control.
Oracle Projects provides a template package that contains the procedure that you modify to implement transaction control
PAY_ACTION_INFORMATION_API
Action Information
This package contains action information APIs.
PAY_AU_MODULE_PARAMETERS_API
Module Parameters for Australia
This package contains module parameter APIs for Australia.
PAY_AU_MODULE_TYPES_API
Module Types for Australia
This package contains module type APIs for Australia.
PAY_AU_MODULES_API
Modules for Australia
This package contains module APIs for Australia.
PAY_AU_PROCESS_MODULES_API
Process Modules for Australia
This package contains process module APIs for Australia.
PAY_AU_PROCESS_PARAMETERS_API
Process Parameters for Australia
This package contains process parameter APIs for Australia.
PAY_AU_PROCESSES_API
Processes for Australia
This package contains process APIs for Australia.
PAY_BAL_ATTRIB_DEFINITION_API
Balance Attribute Definition
This package contains Pay Balance Attribute definition APIs.
PAY_BAL_ATTRIBUTE_DEFAULT_API
Balance Attribute Default
This package creates the pay balance attribute default.
PAY_BALANCE_ADJUSTMENT_API
Balance Adjustment
This package contains the Balance Adjustment API.
PAY_BALANCE_ATTRIBUTE_API
Balance Attribute
Pay Balance Attribute API.
PAY_BALANCE_CATEGORY_API
Balance Category
This package contains the Balance Category API.
PAY_BALANCE_TYPES_API
Balance Type
This package contains Balance Type APIs.
PAY_BATCH_ELEMENT_ENTRY_API
Batch Element Entry
This package contains Batch Element Entry APIs.
PAY_CA_EMP_FEDTAX_INF_API
Employee Federal Tax Information for Canada
This package contains Federal Tax Information APIs.
PAY_CA_EMP_PRVTAX_INF_API
Employee Provincial Tax Information for Canada
This package contains employee provincial tax information APIs for Canada.
PAY_CITY_TAX_RULE_API
City Tax Rule
This package contains city tax rules APIs.
PAY_CNU_API
Contribution Usage
This package contains Contribution Usage APIs.
PAY_COST_ALLOCATION_API
Cost Allocation
This package contains the Cost Allocation API.
PAY_COUNTY_TAX_RULE_API
County Tax Rule
This package contains county tax rules APIs.
PAY_DATETRACKED_EVENTS_API
DateTrack Events
This package contains APIs for Datetracked Events.
PAY_ELEMENT_ENTRY_API
Element Entry
This package contains element entry APIs.
PAY_ELEMENT_EXTRA_INFO_API
Element Extra Information
This package contains element extra information APIs.
PAY_ELEMENT_LINK_API
Element Link
This package contains element link APIs.
PAY_ELEMENT_TYPE_USAGE_API
Element Type Usage
This package contains element type usage APIs.
PAY_ELEMENT_TYPES_API
Element Type
This package contains element type APIs.
PAY_EVENT_GROUPS_API
Event Groups
This package contains APIs for Event Groups.
PAY_EVENT_PROCEDURES_API
Event Procedure
This package contains APIs for Event Procedures.
PAY_EVENT_QUALIFIERS_API
Event Qualifiers
This package contains APIs for Event Qualifiers.
PAY_EVENT_UPDATES_API
Event Update
This package contains APIs for Event Updates.
PAY_EVENT_VALUE_CHANGES_API
Event Value Change
This package contains APIs for Event Value Changes.
PAY_FEDERAL_TAX_RULE_API
Federal Tax Rule
This package contains federal tax rules APIs.
PAY_FORMULA_RESULT_RULE_API
Formula Result Rule
This package contains Formula Result Rule APIs.
PAY_IE_PAYE_API
PAYE Detail for Ireland
This package contains the PAYE Details API for Ireland.
PAY_IE_PRSI_API
PRSI Detail for Ireland
This package contains the PRSI Details API for Ireland.
PAY_IN_ORG_PAYMENT_METHOD_API
Organization Payment Method for India
This package contains organization payment method APIs.
PAY_INPUT_VALUE_API
Input Value
This package contains input value APIs.
PAY_ITERATIVE_RULES_API
Iterative Rule
This package contains the Iterative Rules API.
PAY_LINK_INPUT_VALUES_API
Link Input Value
This package contains link input value APIs.
PAY_NCR_API
Net Calculation Rule
This package contains Net Calculation Rule APIs.
PAY_ORG_PAYMENT_METHOD_API
Organization Payment Method
This package contains Organization Payment Method APIs.
PAY_PAYROLL_API
Payroll
This package contains Payroll APIs.
PAY_PMED_ACCOUNTS_API
Provincial Medical Account
This package contains Proincial Medical Accounts APIs.
PAY_RUN_TYPE_API
Run Type
This package contains the Create Payroll API.
PAY_RUN_TYPE_ORG_METHOD_API
Run Type Organization Method
This package contains the Run Type Organization Method API.
PAY_RUN_TYPE_USAGE_API
Run Type Usage
This package contains APIs for Run Type Usages.
PAY_STATE_TAX_RULE_API
State Tax Rule
This package contains state tax rules APIs.
PAY_STATUS_PROCESSING_RULE_API
Status Processing Rule
This package contains Status Processing Rule APIs.
PAY_US_TAX_API
Tax for United States
This package contains United States tax details maintenance APIs.
PAY_US_TAX_BALS_ADJ_API
Tax Balance Adjustment for United States
This package contains US Tax Balance Adjustments API.
PAY_USER_COLUMN_API
User Column
This API creates a new user column for an user table.
PAY_USER_COLUMN_INSTANCE_API
User Column Instance
This package contains the User Column Instance APIs.
PAY_USER_ROW_API
User Row
This package contains user row APIs.
PAY_USER_TABLE_API
User Table
This package contains User Table APIs.
PAY_WCI_ACCOUNTS_API
Worker Compensation Account for Canada
This Package contains Workers Compensation Account APIs.
PAY_WCI_OCCUPATIONS_API
Workers Compensation Occupation
This package contains Workers compensation Occupation APIs.
PAY_WCI_RATES_API
Worker Compensation Rate
This package contains WCI rates APIs.
PER_BF_BALANCE_AMOUNTS_API
Backfeed Balance Amount
This package contains APIs that will maintain backfeed balance amounts.
PER_BF_BALANCE_TYPES_API
Backfeed Balance Type
This package contains APIs that will maintain backfeed balance types for
PER_BF_PAYMENT_DETAILS_API
Backfeed Payment Detail
This package contains APIs that maintain backfeed payment details.
PER_BF_PAYROLL_RUNS_API
Backfeed Payroll Run
This package contains APIs that maintain backfeed payroll runs details.
PER_BF_PROC_ASSIGNMENT_API
Backfeed Processed Assignment
This package contains APIs for maintaining backfeed processed assignments
PER_CA_PERSON_ADDRESS_API
Person Address for Canada
This package contains person address APIs.
PER_CANCEL_APPLICATION_API
Cancel Application
This package contains APIs relating to canceling application.
PER_CHECKLIST_ITEMS_API
Checklist Item
This package contains checklist item APIs.
PER_DISABILITY_API
Disability
This package contains APIs which maintain a history of any disabilities a
PER_DK_WORK_INCIDENT_API
Work Incident for Denmark
This package contains work incident APIs for Denmark.
PER_ESTAB_ATTENDANCES_API
Establishment Attendance
This package contains HR Establishment Attendance APIs.
PER_EVENTS_API
Event
This package contains HR Event APIs.
PER_HIERARCHY_API
Generic Hierarchy
This package contains APIs which maintains generic hierarchy records.
PER_HIERARCHY_NODES_API
Generic Hierarchy Node
This package contains APIs which maintain node data (value or leave) records
PER_HIERARCHY_VERSIONS_API
Generic Hierarchy Version
This package contains APIs which create and maintain the structure version
PER_JOB_EXTRA_INFO_API
Job Extra Information
This package contains APIs which create and maintain extra information
PER_JOB_GROUP_API
Job Group
This package contains APIs that create and maintain job group information.
PER_KW_DISABILITY_API
Kuwait Disability APIs
This API creates a disability record for a person.
PER_KW_WORK_INCIDENT_API
Work Incidents for Kuwait
This package contains APIs for work incidents for Kuwait.
PER_MEDICAL_ASSESSMENT_API
Medical Assessment
This package contains APIs which create and maintain medical assessment
PER_MX_DISABILITY_API
Disability for Mexico
This package contains disability API wrappers for Mexico.
PER_MX_WORK_INCIDENT_API
Work Incident for Mexico
This package contains APIs which create and maintain work incident records
PER_NO_WORK_INCIDENT_API
Work Incident for Norway.
This package contains work incidents APIs for Norway.
PER_ORG_STRUCTURE_VERSION_API
Organization Hierarchy Version
This package contains APIs that create and maintain organization hierarchy
PER_ORGANIZATION_STRUCTURE_API
Organization Hierarchy
This package contains APIs that create and maintain organization
PER_POS_STRUCTURE_VERSION_API
Position Hierarchy Version
This package contains APIs that create and maintain Position Hierarchy
PER_POSITION_STRUCTURE_API
Position Hierarchy
This package contains APIs that create and maintain position hierarchies.
PER_QUALIFICATIONS_API
Qualification
This package contains Qualification APIs.
PER_REC_ACTIVITY_FOR_API
Recruitment Activity For
This package contains APIs to create and maintain Association between a
PER_RECRUITMENT_ACTIVITY_API
Recruitment Activity
This package contains HR Recruitment Activity APIs.
PER_REQUISITIONS_API
Requisition
This package contains APIs to create, update and delete vacancy
PER_SA_DISABILITY_API
Disability for Saudi Arabia
This package contains disability APIs for Saudi Arabia.
PER_SA_WORK_INCIDENT_API
Work Incident for Saudi Arabia
This package contains work incident APIs for Saudi Arabia.
PER_SUPPLEMENTARY_ROLE_API
Supplementary Role
This package contains APIs that create and maintain supplementary roles.
PER_VACANCY_API
Vacancy
This package contains HR Vacancy APIs.
PER_WORK_INCIDENT_API
Work Incident
This package contains APIs which create and maintain work incident records
PO_CHANGE_API1_S
Purchase Order Change APIs
This package contains procedures that enables you to record supplier
PO_CUSTOM_PRICE_PUB
Purchase Order Custom Pricing APIs
Provide the ability to perform custom pricing on Oracle Purchasing
PO_DOCUMENT_CONTROL_PUB
Purchase Order Document Control APIs
Provides the ability to cancel Purchasing documents directly through
PQH_ACCOMMODATIONS_API
Accommodation for France
This package contains APIs to create, update and delete accommodations.
PQH_ASSIGN_ACCOMMODATIONS_API
Assign Accommodation for France
This package contains APIs to create, update and delete an employee's
PQH_ATTRIBUTE_RANGES_API
Attribute Range
This package contains attribute range APIs.
PQH_BDGT_CMMTMNT_ELMNTS_API
Budget Commitment Element
This package contains APIs to create, update and delete the elements for
PQH_BDGT_POOL_REALLOCTIONS_API
Budget Pool Reallocation
This API creates the budget pool reallocation transaction detail.
PQH_BUDGET_DETAILS_API
Budget Detail
This package contains APIs to create, update and delete the budget details.
PQH_BUDGET_ELEMENTS_API
Budget Element
This package contains APIs to create, update and delete the elements
PQH_BUDGET_FUND_SRCS_API
Budget Fund Source
This package contains APIs to create, update and delete the Budget Costing
PQH_BUDGET_GL_FLEX_MAPS_API
Budget General Ledger Flexfield Mapping
This package contains APIs to create, update or delete costallocations with
PQH_BUDGET_PERIODS_API
Budget Periods
This package contains APIs to create, update and delete period level
PQH_BUDGET_POOLS_API
Budget Pool
This package contains APIs to create, update or delete reallocation folders
PQH_BUDGET_SETS_API
Budget Set
This package contains APIs to create, update and delete the budget sets for
PQH_BUDGET_VERSIONS_API
Budget Version
This package contains APIs to create, update or delete budget versions.
PQH_BUDGETS_API
Budget
This package contains APIs to create, update and delete budgets.
PQH_CORPS_DEFINITIONS_API
CORPS Definition for France
This package contains APIs to validate, create, update and delete corps
PQH_CORPS_EXTRA_INFO_API
CORPS Extra Information for France
This package contains APIs to validate, create, update and deletecorps
PQH_DFLT_BUDGET_ELEMENTS_API
Default Budget Element
This package contains APIs to create, update and delete the default budget
PQH_DFLT_BUDGET_SETS_API
Default Budget Set
This package contains APIs to create, update and delete a default budget
PQH_DFLT_FUND_SRCS_API
Default Funding Source
This package contains APIs to create, update and delete the default funding
PQH_FR_GLOBAL_PAYSCALE_API
Global Payscale for France
This package contains global payscale APIs.
PQH_FR_STAT_SIT_RULES_API
Statutory Situation Rule for France
This package contains APIs to validate, create, update and delete statutory
PQH_FR_STAT_SITUATIONS_API
Statutory Situation for France
This package contains APIs to validate, create, update and delete statutory
PQH_FR_VALIDATION_EVENTS_API
Validation Event for France
This package contains APIs to create, update and delete events in a services
PQH_FR_VALIDATION_PERIODS_API
Service Validation Period for France
This package contains APIs to create, update and delete service periods.
PQH_FR_VALIDATIONS_API
Service Validation for France
This package contains APIs to create, update and delete services validation.
PQH_REF_TEMPLATES_API
Reference Templates
This package contains reference template APIs.
PQH_ROLE_EXTRA_INFO_API
Role Extra Information
This package contains role extra information APIs .
PQH_ROLE_TEMPLATES_API
Role Template
This package contains role template APIs.
PQH_ROLES_API
Role
This package contains role APIs.
PQH_ROUTING_CATEGORIES_API
Routing Category
This package contains routing category APIs.
PQH_ROUTING_LIST_MEMBERS_API
Routing List Member
This package contains routing list members APIs.
PQH_ROUTING_LISTS_API
Routing List
This package contains routing list APIs.
PQH_RULE_ATTRIBUTES_API
Rule Attribute
This package contains rule attribute APIs.
PQH_RULE_SETS_API
Rule Set
This package contains rule set APIs.
PQH_SITUATIONS_API
Situation for France
This package contains APIs to validate, create, update and delete seniority
PQH_TEMPLATE_ATTRIBUTES_API
Template Attribute
This package contains template attribute APIs.
PQH_TEMPLATES_API
Transaction Template
This package contains transaction template APIs.
PQH_TRAN_CATEGORY_API
Transaction Category
This package contains transaction category API.
PQH_TXN_CAT_ATTRIBUTES_API
Transaction Category Attribute
This package contains transaction category attribute API.
PQP_PCV_API
Configuration Value
This package contains APIs for configuration values.
PQP_SHP_API
Employment Service History
This package contains employment service history API's.
PQP_VEH_ALLOC_EXTRA_INFO_API
Vehicle Allocation Extra Information
This package contains vehicle allocation extra information APIs.
PQP_VEH_REPOS_EXTRA_INFO_API
Vehicle Repository Extra Information
This package contains vehicle repository extra information APIs.
PQP_VEHICLE_ALLOCATIONS_API
Vehicle Allocation
This package contains APIs to create, update or delete a Vehicle Allocation
PQP_VEHICLE_REPOSITORY_API
Vehicle Repository
This package contains vehicle repository APIs that can be used to create,
PQP_AAT_API
Assignment Attribute
This package contains APIs for assignment attributes.
PSA_FUNDS_CHECKER_PKG
PSA Funds Checker
PSA_FUNDS_CHECKER_PKG is an online funds checker that enforces budgetary control.
PSP_EFF_REPORT_DETAILS_API
Effort Report Detail
This package contains Update API for Effort Report Details.
PY_ELEMENT_ENTRY_API
Element Entry
This package contains obsolete element entry APIs.
QA_PLANS_PUB
Collection Plan Setup
This package is the public interface for Quality Collection Plans setup.
QA_SPECS_PUB
Specifications Definition
This package is the public interface for Quality Specifications setup.
QP_CURRENCY_PUB
Multi-Currency Conversion Setup
This package consists of entities to set up Multi-Currency Conversion.
QP_CUSTOM
Custom Pricing
This package contains the specification for the GET_CUSTOM_PRICE API.
QP_MODIFIERS_PUB
Modifier Setup
This package consists of entities to set up modifiers.
QP_PREQ_PUB
Price Request
This package contains procedures to be called to pass the request information
QP_PRICE_FORMULA_PUB
Formula Setup
This package consists of entities to support the formulas window.
QP_PRICE_LIST_PUB
Price List Setup
This package consists of entities to set up price lists.
QP_QUALIFIER_RULES_PUB
Qualifier Setup
This package consists of entities to set up qualifiers.
QP_SECURITY
Object Security
This package contains a function to determine functional object access for a
UMX_PUB
User Management Public Interface
This is the public interface that provides APIs to execute various
USER_PKG_LOT
User Defined Lot Generation API
The user defined lot generation procedures allow a user to create a lot
USER_PKG_SERIAL
User Defined Serial Generation API
The user defined serial generation procedures allow users to create Serial
WF_AGENT_GROUPS_PKG
Workflow Agent Group Member
Provides APIs to communicate agent group member definitions to and
WF_AGENTS_PKG
Workflow Agent
Provides APIs to communicate agent definitions to and from the
WF_BES_CLEANUP
Workflow Control Queue Cleanup
Cleans up the standard WF_CONTROL queue in the Business Event System
WF_CORE
Workflow Core
Provides APIs that can be called by an application
WF_DIRECTORY
Workflow Directory Services
Provides APIs that can be
WF_ENGINE
Workflow Engine
Provides APIs that can be called by an application program
WF_ERROR_QH
Workflow Error Queue Handler
Handles business event messages on error queues that use the WF_EVENT_T
WF_EVENT
Workflow Business Event System
Provides APIs to communicate with the Business Event System and manage
WF_EVENT_FUNCTIONS_PKG
Workflow Event Functions
Provides utility functions to communicate with the Business Event
WF_EVENT_GROUPS_PKG
Workflow Event Group Member
Provides APIs to communicate event group member definitions to and
WF_EVENT_OJMSTEXT_QH
Workflow JMS Text Queue Handler
Handles business event messages on queues that use the
WF_EVENT_QH
Workflow Event Queue Handler
Handles business event messages on queues that use the WF_EVENT_T
WF_EVENT_SUBSCRIPTIONS_PKG
Workflow Event Subscription
Provides APIs to communicate event subscription definitions to and
WF_EVENTS_PKG
Workflow Event
Provides APIs to communicate event definitions to and from the
WF_MAIL
Workflow Message Subsystem
Provides APIs to access the Oracle Workflow message subsystem and send messages through e-mail.
WF_MAIL_UTIL
Workflow Notification Mailer Utility
Provides notification mailer utility APIs to perform conversions of
WF_MONITOR
Workflow Monitor
This public interface provides APIs to retrieve an
WF_NOTIFICATION
Workflow Notification System
Provides APIs to access the Oracle Workflow Notification
WF_PREF
Workflow Preference
Provides an API to retrieve user preference information.
WF_PURGE
Workflow Purge
Provides APIs to purge obsolete runtime data for
WF_QUEUE
Workflow Queues
Provides APIs that can be called by an application program or a
WF_RULE
Workflow Event Subscription Rule Function
Provides standard rule functions that you can assign to event
WF_SYSTEMS_PKG
Workflow System
Provides APIs to communicate system definitions to and from the
WF_XML
Workflow XML Message Processing Subsystem
Provides APIs to access the Oracle Workflow XML message processing subsystem.
WIP_EAM_WORKREQUEST_PUB
Work Request Import
This package is used for importing the Work Requests.
WMS_CONTAINER_PUB
License Plate Number APIs for WMS
This object handles the creation and updating license plate numbers
WMS_DEVICE_INTEGRATION_PUB
User defined Device Interfacing API for WMS
User defined device integration procedures allows user to process the WMS
WMS_INSTALL
WMS Install
This package provides routine to verify that Oracle Warehouse Management
WMS_RFID_DEVICE_PUB
RFID/barcode read processing APIs for WMS
This object processes EPC/LPN data read through RFID or barcode reader.
WMS_SHIPPING_TRANSACTION_PUB
Integraion object between Shipping and WMS
These procedures act as an integration between Shipping and WMS
WSH_CONTAINER_PUB
Container
This package provides the APIs for execution of various container functions,
WSH_DELIVERIES_PUB
Delivery
This is the public interface for the Delivery entity.
WSH_DELIVERY_DETAILS_PUB
Delivery Line
This is the public interface for the Delivery Line entity.
WSH_EXCEPTIONS_PUB
Exceptions
This package provides the APIs to create Exceptions for delivery lines, deliveries, trips
WSH_FREIGHT_COSTS_PUB
Freight Cost
This is the public interface for freight costs.
WSH_PICKING_BATCHES_PUB
Picking Batch
This is the public interface for picking batches.
WSH_TRIP_STOPS_PUB
Trip Stop
This is the Stop Public Application Program Interface.
WSH_TRIPS_PUB
Trip
This is the Trip Public Application Program Interface.
XDP_INTERFACES_PUB
Service Fulfillment Order Processing
A public interface for Service Fulfillment Manager that is used to submit,
XNB_BILL_SUMMARIES_PKG
Create Bill Summary
This is the public interface of TBI that is used for inserting, creating, or populating

SQL PLSQL Code Review Check List ::

2016-01-18T22:53:00.000+05:30

SQL PLSQL Code Review Check List ::
The package/procedure/function names conform to Oracle standards(If the script is business unit specific then use the BU qualifier in the name of the script).
Creation script file name is same as the Package/Procedure/Function name and the file extension conforms to Oracle standards.
The Package Specification and Package Body are in separate files.
There is only one package spec or body created with each script.
Script contains the Oracle Copyright/Header for version control tool
Internal Documentation exists and it is clear and complete. (Ex. Object details and version history sections).
The documentation exists in the creation portion of the package/procedure/function.
No passwords are hard-coded in the file.
Show Errors exists at the end of the scripts.
The package/procedure/function is formatted for readability and fit standards.
Thereis sufficient in-line documentation. The documentation describes the business functionality and the action the queries perform.
Who columns are updated correctly. (Who Columns include Last_Updated_By, Last_Update_Date...etc)
All SQL statements are multi-org compliant (i.e. no _ALL tables). In case there is a need to use _all tables explain the business requirement in the Notes Column.
No Drop statement should Exist in the Package.
The code uses Oracle APIs or makes call to Standard Interfaces and no direct base table inserts/updates exist.(Exception for user hook in HRMS)
Explain Plan for the top five queries and the trace file (with SORT option) is attached to the iSupport SR.
No Standard Oracle views (except Multi Org Views) should be referred in the code.
Usage of Dynamic SQL in the code is not recommended because it may cause High Literal issue. So It should use bind variable to avoid high parsing (in where clause of SQL).
Parallel hint is not present in the code to avoid performance issues.
Rule hint is not present in the code to avoid performance issues.

Oracle Form Installation Error :: forms60.vrf(78):OS_ERROR while getting value Path

2016-01-18T22:43:00.001+05:30

Oracle Form Installation Error :: forms60.vrf(78):OS_ERROR while getting value Path

When you try installing forms you may get the error: forms60.vrf(78):OS_ERROR while getting value Path

This error has got to do with the character length of Path Environment Variable Field. For Forms6i installation to happen; the character length in the field should be less than 1000. You can definitely cant keep counting to check this. So best is to temporarily cut a portion of the Environment Variable until the installation is completed.

If you are an Oracle Professional; I need not tell you where the environment variable is; for the rest please find below instructions

Right Click My Computer – Properties

Advanced System Settings

Under Advanced Tab; Click Environment Variables

Under System Variables; look for variable named Path

Select Path and click Edit

Copy the contents in the Variable Value to any text editor like notepad

Copy and replace a small portion of the variable value back into the field

Start your forms6i installation and once done revert back the Variable Value from your notepad file

Thanks...............

Current market Requirement as Technical Consultant

2016-01-18T22:34:00.005+05:30

Technical Skill Sets

Overview

· Oracle Applications Techno functional Consultant with 5+ years of relevant experience

Essential

· Extensive working experience in R12 Oracle Market to Cash cycle ( Quoting, Sales contracts, Service contracts, Installed Base, Accounts receivable, Inventory, Advanced Pricing) in Maintenance and support projects

· At least one end to end implementation of Oracle R12 CRM Modules

Technical knowledge

· Candidate should be expert in RICE (Reports Interface Conversions Extensions) components and Workflow

· Should have very good customization experience in OAF ( Oracle applications framework )

Functional exposure

· Good knowledge of setups and process flows in Quoting, Sales contracts, Service contracts, Installed Base, Accounts receivable, Inventory, Advanced Pricing Modules

Others

· Ability to Produce Technical documents MD070 and Testing scenarios

· Excellent oral and written English communication skills required

· Ability to manage, track and progress their own project tasks related work

· work as an individual contributor with minimum guidance required

Preferred

· Oracle Developer certification

· Good understanding and working experience of Oracle AIM methodology.

· Process Oriented and ability to produce quality deliverables

Behavioral Skill Sets

Essential

· Work history demonstrates: Good analytical ability, Should work independently on assigned tasks with quick turnaround time and Good team player

External Description Primary Responsibilities:

· Atleast 4+ years of techno functional experience in R12i Support and maintenance projects

· Handling the technical and Functional issues in Quoting, Sales contracts, Service contracts, Installed Base, Accounts receivable, Inventory, Advanced Pricing Modules

· Exposure to any incident management tool viz CA service desk, clarify etc. will be an added advantage

Secondary Responsibilities:

· Work on Business process/ functionality changes and Enhancements in RICE components, Workflow, OAF and/ or BI publisher

Job Requirements:

· 4-8 years of relevant experience in support and maintenance of R12/ 11i Oracle apps ERP as a technofunctional developer.

Work on Business process/ functionality changes and Enhancements in RICE components and Workflow. Working knowledge in OAF and/ or BI publisher is preferred.

:) :) :)

ROS ERROR :: 200 in Oracle FORM

2016-01-18T22:27:00.000+05:30

Hi team,
If you faced ROS ERROR :: 200 in Oracle FORM then you are using form 10 G form in Form 6i Form Builder.

it's inconsistency issue as you are using higher version form in Lower version and trying to complie it and open it.

Thanks,
Nilesh

log::;

Forms 6.0 (Form Compiler) Version 6.0.8.28.0 (Production)
Forms 6.0 (Form Compiler): Release - Production
(c) Copyright 1999 Oracle Corporation. All rights reserved.

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

PL/SQL Version 8.0.6.3.0 (Production)

Oracle Procedure Builder V6.0.8.21.0 Build #0 - Production

Oracle Virtual Graphics System Version 6.0.5.39.0 (Production)

Oracle Multimedia Version 6.0.8.25.0 (Production)

Oracle Tools Integration Version 6.0.8.17.0 (Production)

Oracle Tools Common Area Version 6.0.5.32.0

Oracle CORE Version 4.0.6.0.0 - Production

ROS ERROR: -200

emsg:was terminated by signal 11

2016-01-18T22:16:00.003+05:30

emsg:was terminated by signal 11

Refer Docs ->
Bitmap Reports Fail Due to Signal 11 and/or hanging after Patchset 19: Emsg:Was Terminated By Signal 11 (Doc ID 759456.1)

PDF Reports Fail With Program Was Terminated By Signal 11 (Doc ID 396336.1)

LOG :: --->

Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
American_America.UTF8

'.,'

stat_low = 8B
stat_high = 0
emsg:was terminated by signal 11

+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
Program was terminated by signal 11
Concurrent Manager encountered an error while running Oracle*Report for your concurrent request 290528124.

Review your concurrent request log and/or report output file for more detailed information.

+---------------------------------------------------------------------------+
Executing request completion options...

+------------- 1) PUBLISH -------------+
Beginning post-processing of request 290528124 on node USMTNPMDERPDB05 at 11-DEC-2014 16:29:05.
Post-processing of request 290528124 failed at 11-DEC-2014 16:29:05 with the error message:
One or more post-processing actions failed. Consult the OPP service log for details.
+--------------------------------------+

Form Development for R12 ::

2015-05-20T00:12:00.001+05:30

Sending Email using PL/SQL Procedure

2015-05-20T00:07:00.002+05:30

CREATE OR REPLACE PROCEDURE XXX_send_mail (p_to IN VARCHAR2, p_from IN VARCHAR2, p_message IN VARCHAR2, p_smtp_host IN VARCHAR2, p_smtp_port IN NUMBER DEFAULT 25)AS l_mail_conn UTL_SMTP.connection;BEGIN l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port); UTL_SMTP.helo(l_mail_conn, p_smtp_host); UTL_SMTP.mail(l_mail_conn, p_from); UTL_SMTP.rcpt(l_mail_conn, p_to); UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf); UTL_SMTP.quit(l_mail_conn);END;/BEGIN XXX_send_mail(p_to => 'nileshapps@gmail.com',--'roger.drolet@theoicllc.com', --receiver's email id p_from => 'nileshapps@gmail.com', p_message => 'Test Message for Form via OIC Database for Manual Cantrol Form', p_smtp_host => 'oicllc-01.oicllc.net');END;

Sending Email using PL/SQL Procedure

You can send mails using the following UTL_SMTP package
DECLARE v_From VARCHAR2(80) := 'oracle@mycompany.com'; v_Recipient VARCHAR2(80) := 'test@mycompany.com'; v_Subject VARCHAR2(80) := 'test subject'; v_Mail_Host VARCHAR2(30) := 'mail.mycompany.com'; v_Mail_Conn utl_smtp.Connection; crlf VARCHAR2(2) := chr(13)||chr(10);BEGIN v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25); utl_smtp.Helo(v_Mail_Conn, v_Mail_Host); utl_smtp.Mail(v_Mail_Conn, v_From); utl_smtp.Rcpt(v_Mail_Conn, v_Recipient); utl_smtp.Data(v_Mail_Conn, 'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf || 'From: ' || v_From || crlf || 'Subject: '|| v_Subject || crlf || 'To: ' || v_Recipient || crlf || crlf || 'some message text'|| crlf || -- Message body 'more message text'|| crlf ); utl_smtp.Quit(v_mail_conn);EXCEPTION WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);

HOW to load Items to organization, subinventory and locator using API [ID 286339.1]

2015-05-20T00:04:00.002+05:30

Applies to:

Oracle Inventory Management - Version: 11.5.2 to 11.5.9
Information in this document applies to any platform.

Goal

Is there an open interface/API(like MTL_SYSTEMS_ITEM_INTERFACE) in Oracle R11i that can be used to load subinventory data.
Customer are using Dataload(a 3rd party Oracle Applications data loader software), the software can load data without any major problem but the processes are too slow and no error controls of the loading. Therefore, customer want to know if that is an interface for subinventory

Solution

If the Org for which you want to transfer the info , WMS enabled, then the CREATE_LOC_ITEM_TIE procedure, under Locator Maintenance APIs, may be useful for this issue;
Note that the list of public API's are all that are available thru the Oracle Inventory Apps;

The API quoted is the only API for this functionality ( locators ) Items that are not locator controlled, can be assigned to subinventories thru the normal Item Open Interface;

An enhancement request was filed for this issue previously;

Note that the Technical Reference Manual shows the table MTL_ITEM_SUB_INVS_INTERFACE and the explanation of the process; Please note, the TRM also states that this table is not used;

The following process was established;

spool msi_subinv.lst
PROMPT msi_subinv.sql

PROMPT Current counts in MTL_SYSTEM_ITEMS_INTERFACE
select process_flag, count(*) from MTL_ITEM_SUB_INVS_INTERFACE group by process_
flag;

prompt
prompt Assigning an item to a subinventory.
prompt
accept YourItemID DEFAULT '11175' prompt 'Please enter your inventory item id
(Default 11175): '
prompt
prompt
accept YourOrgID DEFAULT '207' prompt 'Please enter your Org Code (Default 207):
'
prompt

prompt
accept YourSubinv DEFAULT 'FGI' prompt 'Please enter a Subinventory Code
(Default FGI): '
prompt

insert into MTL_ITEM_SUB_INVS_INTERFACE
(
TRANSACTION_TYPE,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SECONDARY_INVENTORY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PRIMARY_SUBINVENTORY_FLAG,
PICKING_ORDER,
MIN_MINMAX_QUANTITY,
MAX_MINMAX_QUANTITY,
INVENTORY_PLANNING_CODE,
FIXED_LOT_MULTIPLE,
MINIMUM_ORDER_QUANTITY,
MAXIMUM_ORDER_QUANTITY,
SOURCE_TYPE,
SOURCE_ORGANIZATION_ID,
SOURCE_SUBINVENTORY,
PROCESS_FLAG,
ORGANIZATION_CODE,
ITEM_NUMBER
)
VALUES
(
'CREATE', --TRANSACTION_TYPE
&YourItemID, -- INVENTORY_ITEM_ID,
&YourOrgID, -- ORGANIZATION_ID,
'&YourSubinv', -- SECONDARY_INVENTORY,
sysdate,-- LAST_UPDATE_DATE,
-1, -- LAST_UPDATED_BY,
sysdate, -- CREATION_DATE,
-1, -- CREATED_BY,
-1, -- LAST_UPDATE_LOGIN,
NULL, -- PRIMARY_SUBINVENTORY_FLAG,
NULL, -- PICKING_ORDER,
NULL, -- MIN_MINMAX_QUANTITY,
NULL, -- MAX_MINMAX_QUANTITY,
NULL, -- INVENTORY_PLANNING_CODE,
NULL, -- FIXED_LOT_MULTIPLE,
NULL, -- MINIMUM_ORDER_QUANTITY,
NULL, -- MAXIMUM_ORDER_QUANTITY,
NULL, -- SOURCE_TYPE,
NULL, -- SOURCE_ORGANIZATION_ID,
NULL, -- SOURCE_SUBINVENTORY,
NULL, -- PROCESS_FLAG = PENDING
'&YourMasterOrgCode', -- ORGANIZATION_CODE,
NULL -- ITEM_NUMBER
);

spool off

API to Load/maintain the Item Subinventory Information [ID 1458355.1]

2015-05-20T00:03:00.002+05:30

Applies to:

Oracle Inventory Management - Version 11.5.10.CU2 to 12.1.3 [Release 11.5.10 to 12.1]
Information in this document applies to any platform.

Goal

Need an API to assign item only to subinventory. If the API not exist, it is possible to expand the package inv_loc_wms_pub with API to assign items to subinventory?

Fix

Currently there is no supported interface or public API for connecting items to subinventories in the mtl_item_sub_inventories table. ORACLE does not support direct insert in mtl_item_sub_inventories table.

There is an opened Enhancement Request bug for this:
Bug 5380622: ITEM SUBINVENTORY LOCATOR RELATIONSHIP API

Within the ER the following workaround has been stated:
INV_LOC_WMS_PUB.CREATE_LOC_ITEM_TIE is useful only if the org is wms controlled because then the organization is mandatorily locator controlled. You can download the MFGAPI to get more information. Though MTL_ITEM_SUB_INVS_INTERFACE exists, currently we do not support this interface table.

As per the documentation for the API-INV_LOC_WMS_PUB.CREATE_LOC_ITEM_TIE, we can suggest two ways to accomplish the goal of assigning items to a subinventory.

1. Create a locator using - create_locator API, assign the items to subinventory using CREATE_LOC_ITEM_TIE and then delete the locators using delete_locator api - all part of the INV_LOC_WMS_PUB API package.
This is a roundabout route for accomplishing a simple job of assigning items to subinventory, that affects only the mtl_item_sub_inventories table.

2. Pertaining to the body of the API package -Create_LOC_ITEM_TIE code, the below part of the code can be the only one required assigning an item to the subinventory.

**************************************************************************
/* Check if an entry exists in MTL_ITEM_SUB_INVENTORIES table for the item
passed */
BEGIN
SELECT 1
INTO l_item_sub
FROM mtl_item_sub_inventories
WHERE inventory_item_id = l_inventory_item_id
AND secondary_inventory = p_subinventory_code
AND organization_id = l_organization_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO mtl_item_sub_inventories
(inventory_item_id
, organization_id
, secondary_inventory
, last_update_date
, last_updated_by
, creation_date
, created_by
, inventory_planning_code)
VALUES (l_inventory_item_id
, l_organization_id
, p_subinventory_code
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, 6);
END;
******************************************************************************

Note: The workarounds mentioned in the above <> are also not supported.

References

NOTE:286339.1 - HOW to load Items to organization, subinventory and locator using API
BUG:5380622 - ITEM SUBINVENTORY LOCATOR RELATIONSHIP API

ROWID & ROWNUM Pseudocolumn

2014-11-27T15:27:00.001+05:30

What is ROWID Pseudocolumns ::

For each row in the database, the ROWID pseudocolumn returns the address of the row. Oracle Database rowid values contain information necessary to locate a row:

The data object number of the object
The data block in the datafile in which the row resides
The position of the row in the data block (first row is 0)
The datafile in which the row resides (first file is 1). The file number is relative to the tablespace.

Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.

Values of the ROWID pseudocolumn have the datatype ROWID or UROWID. Please refer to "ROWID Datatype" and "UROWID Datatype"for more information.

Rowid values have several important uses:

They are the fastest way to access a single row.
They can show you how the rows in a table are stored.
They are unique identifiers for rows in a table.

You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later.

Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudocolumn.

Don't use ROW_ID with MIN and MAX as MAX(ROWID) won't always return me last inserted row because that row should not have the biggest ROWID value.

The value of ROW_ID is not in sequence but it's only unique value.

What is ROWNUM Pseudocolumns ::

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM employees WHERE ROWNUM < 10;

If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. For example, if the ORDER BY clause causes Oracle to use an index to access the data, then Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example:

SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force theROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the employees with the 10 smallest employee numbers. This is sometimes referred to as top-N reporting:

SELECT * FROM

(SELECT * FROM employees ORDER BY employee_id)

WHERE ROWNUM < 11;

In the preceding example, the ROWNUM values are those of the top-level SELECT statement, so they are generated after the rows have already been ordered by employee_id in the subquery.

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT * FROM employees

WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

UPDATE my_table

SET column1 = ROWNUM;

Please refer to the function ROW_NUMBER for an alternative method of assigning unique numbers to rows.

Sources :: https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm

Oracle Business Intelligence Developers Guide (Oracle Press)

2014-11-26T14:55:00.000+05:30

Overview

Main description

Master Oracle Business Intelligence 11g Reports and Dashboards

Deliver meaningful business information to users anytime, anywhere, on any device, using Oracle Business Intelligence 11g. Written by Oracle ACE Director Mark Rittman, Oracle Business Intelligence 11g Developers Guide fully covers the latest BI report design and distribution techniques. Find out how to execute effective queries, build accurate models, use scorecards and KPIs, create dynamic reports, set up dashboards, and publish to smartphones and wireless devices. This Oracle Press guide contains comprehensive details on Oracle Exalytics In-Memory Machine, the best-in-class, preintegrated BI platform.

Install or upgrade to Oracle Business Intelligence 11g
Develop and manage custom Oracle Business Intelligence repositories
Access relational, file, and multidimensional data sources
Design print-quality reports with Oracle Business Intelligence Publisher
Create web-enabled analyses, dashboards, and visualizations
Integrate with other applications using Oracle Business Intelligence 11g Action Framework
Employ authentication, authorization, and row-level security
Configure and deploy Oracle Exalytics In-Memory Machine

1. Overview and Product Architecture
2. Installations and Upgrades, for the Oracle BI Developers Handbook
3. Creating Repositories from Relational Sources
4. Creating Repositories from Oracle Essbase and Other OLAP Data Sources
5. Configuring and Maintaining the BI Server
6. Creating Reports Using Answers and Dashboards
7. Actionable Intelligence
8. KPIs and Scorecards
9. Creating Published Reports (BI Publisher)
10. Systems Management
11. High Availability, Clustering and Backup & Recovery
12. Managing Change, Versioning and Promotion Between Environments
13. Oracle Exalytics Business Intelligence Machine

Author comments

Mark Rittman, Oracle ACE Director, is Technical Director and co-founder of Rittman Mead, specializing in BI, DW and EPM solutions using Oracle Business Intelligence, Oracle Database, Oracle Data Integrator and Oracle Essbase. Mark is a strong supporter of Oracle user groups around the world, was previously an executive board member of ODTUG and speaks regularly at conferences in the UK, Europe, USA and around the world. Mark also writes for the Rittman Mead blog (http://www.rittmanmead.com/blog) and contributes a regular column on business intelligence for Oracle Magazine.

Original Source:: http://www.rittmanmead.com/obiee11gbook/

http://www.mhprofessional.com/product.php?isbn=0071798749

Oracle Apps Testing

2014-02-11T19:33:00.002+05:30

1 Testing::

The ORACLE Apps testing strategy defines five levels of testing. The ORACLE Testing Strategy deliverable contains detailed information on each testing level. The Developer’s Handbook only provides brief explanations for developers to use as a quick reference guide. The Developer’s Handbook does provide a minimal level of additional detail for those levels of testing in which developers themselves play a primary role. The levels of testing include:

· Construction Testing, which focuses on RICE elements and application configuration.

· Process Testing, which verifies delivered and enhanced software functionality.

· Integration Testing, which validates end-to-end business processes

· User Acceptance Testing, which validates the solution to the end-user community

· Technical Testing, which validate the hardware, software, and ancillary components function properly

1.1 Construction Testing

1.1.1 Unit Testing

Although not a separate deliverable from the code itself, developers will perform unit testing on each RICE component. The developer will be responsible for defining and executing the unit test for the development item. Unit tests will validate required functionality as detailed in the functional requirements document and that adequate exception handling is included in the developed item. Unit testing focuses on detailed testing of all paths through a piece of code. Developers should place emphasis on error processing. For example, when testing a custom form, developers should place special characters in all fields and have the form attempt to process. Upon successful completion of unit testing for a RICE component, the developers will conduct string testing on the component.

Developers perform all testing.

1.1.2 String Testing

String testing involves both the source and target systems for interfaces and conversions. In sting testing, the source application produces output, whether a flat file or a message. The target application receives and processes the input file or message. String testing proves that data elements transfer from the source to the target application. Upon successful completion of string testing, the technical team makes the components available for processing testing.

Developers from both the source and target divisions perform string testing.

1.1.3 Application Configuration Testing

Application configuration testing verifies the settings within the Oracle Applications. The tests ensure that the configured application meets documented requirements. Application configuration testing does not involve RICE components.

Developers have no responsibility for application configuration testing, although they may need to assist functional team members.

1.2 Process Testing

Members of the process project team will define and execute individual process tests on each function. Process testing combines the configured application and the RICE components into an integrated environment. Process testing takes place within the ORACLE TEAM and does not focus on division specific data. Upon successful completion of process testing a RICE component, the process team will make the development package available for Integration testing.

Developers do not create or execute process test. However, developers will investigate potential errors uncovered during testing and will resolve coding or Oracle bug issues as necessary.

1.3 Integration Testing

During integration testing, the process team performs end-to-end testing on processes. During this testing cycle, tests interfaces with the legacy application and integration with third parties, such as a bank. Upon successful completion of integration testing a RICE component, the process team will make the development package available for user acceptance testing.

1.4 User Acceptance Testing

’s user acceptance testing performs “a day in the life” testing scenario. For example, users execute daily, weekly, and monthly processes. ’s user community will perform user acceptance testing on each RICE component. User acceptance tests will validate required functionality as detailed in the functional requirements document. Upon successful completion of acceptance testing for a RICE component, the end-users will make the development package available for production.

1.5 Technical Testing

1.5.1 Production Readiness Testing

Production readiness testing executes one time, immediately before the first production implementation. Production readiness testing occurs on the production hardware using the production software. Production readiness testing enables to test the exact hardware and software configurations that will exist after deployment to the Oracle Application. Production readiness testing focuses on validating that all the proper connections exist between the Oracle Application environment and other systems, whether internal or external to . (The UAT/FAT3 tests will serve as Production Readiness testing for the ORACLE implementation.)

1.5.2 Stress Testing

The ORACLE TEAM will conduct stress testing before the implementation of the pilot division, EPT. Stress testing will send a high volume of transactions through the system to test the performance of the application components. In the case of the ORACLE TEAM, stress testing will focus primarily on the interfaces.

1.5.3 Patch Testing

From a technical perspective, patch testing allows the person who applies the patch to ensure that repair software from the supplier will install completely and successfully and that the system is still accessible online. In addition, by having the functional resources participate, patch testing validates the functionality of an application after receiving a software update from the supplier. Patch testing identifies whether the patch fixes the known problem or not. In addition, patch testing also involves verifying that the patch does adversely affect any other software functionality.

1.5.4 Infrastructure Verification

Infrastructure verification testing validates whether new hardware platforms or network connections function as expected. Infrastructure verification testing only occurs when new hardware platforms or major network upgrades exist.

Oracle Reserved Words

2014-02-11T19:27:00.001+05:30

1 Appendix A – Oracle Reserved Words

1.1 Oracle 8.1.7 Reserved Words

This appendix lists Oracle reserved words. Oracle changes reserved words with each version of its product. For a complete listing of reserved words, developers must use https://metalink.oracle.com. Words followed by an asterisk (*) are also ANSI reserved words. Developers must only use these words in Oracle related context.

Note: In addition to the following reserved words, Oracle uses system-generated names beginning with "SYS_" for implicitly generated schema objects and sub-objects. Oracle discourages developers from using this prefix in the names explicitly given to custom schema objects and sub-objects to avoid possible conflict in name resolution.

ACCESS

ADD*

ALL*

ALTER*

AND*

ANY*

AS*

ASC*

AUDIT

BETWEEN*

BY*

CHAR*

CHECK*

CLUSTER

COLUMN

COMMENT

COMPRESS

CONNECT*

CREATE*

CURRENT*

DATE*

DECIMAL*

DEFAULT*

DELETE*

DESC*

DISTINCT*

DROP*

ELSE*

EXCLUSIVE

EXISTS

FILE

FLOAT*

FOR*

FROM*

GRANT*

GROUP*

HAVING*

IDENTIFIED

IMMEDIATE*

IN*

INCREMENT

INDEX

INITIAL

INSERT*

INTEGER*

INTERSECT*

INTO*

IS*

LEVEL*

LIKE*

LOCK

LONG

MAXEXTENTS

MINUS

MLSLABEL

MODE

MODIFY

NOAUDIT

NOCOMPRESS

NOT*

NOWAIT

NULL*

NUMBER

OF*

OFFLINE

ON*

ONLINE

OPTION*

OR*

ORDER*

PCTFREE

PRIOR*

PRIVILEGES*

PUBLIC*

RAW

RENAME

RESOURCE

REVOKE*

ROW

ROWS*

ROWID

ROWNUM

SELECT*

SESSION*

SET

SIZE

SMALLINT*

START

SUCCESSFUL

SYNONYM

SYSDATE

TABLE*

THEN*

TO*

TRIGGER

UID

UNION*

UNIQUE*

UPDATE*

USER*

VALIDATE

VALUES*

VARCHAR*

VARCHAR2

VIEW*

WHENEVER*

WHERE

WITH*

1.2 PL/SQL 8.1.7 Reserved Words

PL/SQL reserves the words listed in this appendix. That is, they have a special syntactic meaning to PL/SQL. Therefore, developers should not use them to name program objects such as constants, variables, or cursors. SQL also reserves some of these words (marked by an asterisk). Therefore, developers should not use them to name schema objects such as columns, tables, or indexes. Oracle changes reserved words with each version of its product. For a complete listing of reserved words, developers must use https://metalink.oracle.com.

ALL*

ALTER*

AND*

ANY*

ARRAY

AS*

ASC*

AUTHID

AVG

BEGIN

BETWEEN*

BINARY_INTEGER

BODY

BOOLEAN

BULK

BY*

CHAR*

CHAR_BASE

CHECK*

CLUSTER*

COLLECT

COMMENT*

COMMIT

COMPRESS*

CONNECT*

CONSTANT

CREATE*

CURRENT*

CURRVAL

CURSOR

DATE*

DAY

DECIMAL*

DECLARE

DEFAULT*

DELETE*

DESC*

DISTINCT*

DROP*

ELSE*

ELSIF

END

EXCEPTION

EXCLUSIVE*

EXECUTE

EXISTS*

EXIT

EXTENDS

FALSE

FETCH

FLOAT*

FOR*

FORALL

FROM*

FUNCTION

GOTO

GROUP*

HAVING*

HEAP

HOUR

IMMEDIATE*

IN*

INDEX*

INDICATOR

INSERT*

INTEGER*

INTERFACE

INTERSECT*

INTERVAL

INTO*

IS*

ISOLATION

JAVA

LEVEL*

LIKE*

LIMITED

LOCK*

LONG*

LOOP

MAX

MIN

MINUS*

MINUTE

MLSLABEL*

MOD

MODE*

MONTH

NATURAL

NATURALN

NEW

NEXTVAL

NOCOPY

NOT*

NOWAIT*

NULL*

NUMBER*

NUMBER_BASE

OCIROWID

OF*

ON*

OPAQUE

OPEN

OPERATOR

OPTION*

OR*

ORDER*

ORGANIZATION

OTHERS

OUT

PACKAGE

PARTITION

PCTFREE*

PLS_INTEGER

POSITIVE

POSITIVEN

PRAGMA

PRIOR*

PRIVATE

PROCEDURE

PUBLIC*

RAISE

RANGE

RAW*

REAL

RECORD

REF

RELEASE

RETURN

REVERSE

ROLLBACK

ROW*

ROWID*

ROWNUM*

ROWTYPE

SAVEPOINT

SECOND

SELECT*

SEPARATE

SET*

SHARE*

SMALLINT*

SPACE

SQL

SQLCODE

SQLERRM

START*

STDDEV

SUBTYPE

SUCCESSFUL*

SUM

SYNONYM*

SYSDATE*

TABLE*

THEN*

TIME

TIMESTAMP

TO*

TRIGGER*

TRUE

TYPE

UID*

UNION*

UNIQUE*

UPDATE*

USE

USER*

VALIDATE*

VALUES*

VARCHAR*

VARCHAR2*

VARIANCE

VIEW*

WHEN

WHENEVER*

WHERE*

WHILE

WITH*

FRM-10102: Cannot attach PL/SQL library APPCORE

2014-01-16T10:37:00.003+05:30

Problem :: FRM-10102: Cannot attach PL/SQL library APPCORE. This library attachment will be lost if the module is saved

Solution :: Need to add FORMS_PATH in registry of windows.

Press Win R to open windows Run and put regedit to edit the windows registry.
and search ofr FORMS_PATH
and custom Forms and plls path in this variable.

Query for Extracting Employee Information:

2014-01-15T10:07:00.003+05:30

Query for Employee Information: Oracle Apps Employee Data Extract :

SELECT p.FULL_NAME,p.employee_number Employee_Number,
p.last_name Last_Name,
p.first_name First_Name,
p.original_date_of_hire Hire_date,
p.DATE_OF_BIRTH DOB,
p.SEX Gender,
p.EMAIL_ADDRESS EMAIL_Address,
(SELECT name
FROM apps.hr_all_organization_units
WHERE organization_id = p.business_group_id)
Organization,
(SELECT location_code
FROM apps.hr_locations_all_tl
WHERE location_id = a.location_id
AND language = USERENV ('LANG'))
Location_Name,
'A' Status_Flag,
(SELECT CONCATENATED_SEGMENTS
FROM apps.GL_CODE_COMBINATIONS_KFV
WHERE code_combination_id = a.DEFAULT_CODE_COMB_ID)
Expense_Account,
(SELECT papf1.full_name supervisor_name
FROM apps.per_all_people_f papf,
apps.per_all_assignments_f paaf,
apps.per_all_people_f papf1
WHERE papf.person_id = paaf.person_id
AND paaf.primary_flag = 'Y'
AND paaf.assignment_type = 'E'
AND paaf.supervisor_id = papf1.person_id
AND papf1.current_employee_flag = 'Y'
AND papf.business_group_id = paaf.business_group_id
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND SYSDATE BETWEEN papf1.effective_start_date
AND papf1.effective_end_date
AND papf.employee_number = p.employee_number
and papf.person_id = p.person_id)
Supervisor
FROM apps.per_all_people_f p,
apps.per_all_assignments_f a,
apps.pay_people_groups ppg,
apps.hr_all_positions_f hap
WHERE P.Person_Id = A.Person_Id
AND SYSDATE BETWEEN P.Effective_Start_Date AND P.Effective_End_Date
AND SYSDATE BETWEEN a.effective_start_date AND a.effective_end_date
AND a.people_group_id = ppg.people_group_id
AND hap.position_id(+) = a.position_id

bdemobld.sql Demo Table Script for Practice

2013-12-14T00:30:00.001+05:30

bdemobld.sql   -- Demo Table Script for Practice...

DROP VIEW SALES;
DROP TABLE ITEM;
DROP TABLE SALES_ORDER;
DROP TABLE CUSTOMER;
DROP TABLE PRICE;
DROP TABLE PRODUCT;
DROP TABLE SALARY_GRADE;
DROP TABLE EMPLOYEE;
DROP TABLE JOB;
DROP TABLE DEPARTMENT;
DROP TABLE LOCATION;

REM Create new datasources

CREATE TABLE LOCATION (
        LOCATION_ID             NUMBER(3),
        REGIONAL_GROUP          VARCHAR2(20));

CREATE TABLE DEPARTMENT (
        DEPARTMENT_ID           NUMBER(2),
        NAME                    VARCHAR2(14),
        LOCATION_ID             NUMBER(3));

CREATE TABLE JOB (
        JOB_ID                  NUMBER(3),
        FUNCTION                VARCHAR2(30));

CREATE TABLE EMPLOYEE (
        EMPLOYEE_ID             NUMBER(4),
        LAST_NAME               VARCHAR2(15),
        FIRST_NAME              VARCHAR2(15),
        MIDDLE_INITIAL          VARCHAR2(1),
        JOB_ID                  NUMBER(3),
        MANAGER_ID              NUMBER(4),
        HIRE_DATE               DATE,
        SALARY                  NUMBER(7,2),
        COMMISSION              NUMBER(7,2),
        DEPARTMENT_ID           NUMBER(2));

CREATE TABLE SALARY_GRADE (
        GRADE_ID                NUMBER(3),
        LOWER_BOUND             NUMBER(7,2),
        UPPER_BOUND             NUMBER(7,2));

CREATE TABLE PRODUCT (
        PRODUCT_ID              NUMBER (6),
        DESCRIPTION             VARCHAR2 (30));

CREATE TABLE PRICE (
        PRODUCT_ID              NUMBER (6),
        LIST_PRICE              NUMBER (8,2),
        MIN_PRICE               NUMBER (8,2),
        START_DATE              DATE,
        END_DATE                DATE);

CREATE TABLE CUSTOMER (
        CUSTOMER_ID             NUMBER (6),
        NAME                    VARCHAR2 (45),
        ADDRESS                 VARCHAR2 (40),
        CITY                    VARCHAR2 (30),
        STATE                   VARCHAR2 (2),
        ZIP_CODE                VARCHAR2 (9),
        AREA_CODE               NUMBER (3),
        PHONE_NUMBER            NUMBER (7),
        SALESPERSON_ID          NUMBER (4),
        CREDIT_LIMIT            NUMBER (9,2),
        COMMENTS                LONG);

CREATE TABLE SALES_ORDER (
        ORDER_ID                NUMBER (4),
        ORDER_DATE              DATE,
        CUSTOMER_ID             NUMBER (6),
        SHIP_DATE               DATE,
        TOTAL                   NUMBER (8,2));

CREATE TABLE ITEM (
        ORDER_ID                NUMBER (4),
        ITEM_ID                 NUMBER (4),
        PRODUCT_ID              NUMBER (6),
        ACTUAL_PRICE            NUMBER (8,2),
        QUANTITY                NUMBER (8),
        TOTAL                   NUMBER (8,2));

CREATE VIEW SALES AS
SELECT SALESPERSON_ID, SALES_ORDER.CUSTOMER_ID, CUSTOMER.NAME CUSTOMER,
       PRODUCT.PRODUCT_ID, DESCRIPTION PRODUCT, SUM(ITEM.TOTAL) AMOUNT
FROM SALES_ORDER, ITEM, CUSTOMER, PRODUCT
WHERE SALES_ORDER.ORDER_ID = ITEM.ORDER_ID
AND SALES_ORDER.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
AND ITEM.PRODUCT_ID = PRODUCT.PRODUCT_ID
GROUP BY SALESPERSON_ID, SALES_ORDER.CUSTOMER_ID, CUSTOMER.NAME,
         PRODUCT.PRODUCT_ID, DESCRIPTION;

REM Grant SELECT privileges to PUBLIC on the datasources

GRANT SELECT ON LOCATION TO PUBLIC;
GRANT SELECT ON DEPARTMENT TO PUBLIC;
GRANT SELECT ON JOB TO PUBLIC;
GRANT SELECT ON EMPLOYEE TO PUBLIC;
GRANT SELECT ON SALARY_GRADE TO PUBLIC;
GRANT SELECT ON PRODUCT TO PUBLIC;
GRANT SELECT ON PRICE TO PUBLIC;
GRANT SELECT ON CUSTOMER TO PUBLIC;
GRANT SELECT ON SALES_ORDER TO PUBLIC;
GRANT SELECT ON ITEM TO PUBLIC;
GRANT SELECT ON SALES TO PUBLIC;

REM Comment the datasources and their columns

COMMENT ON TABLE EMPLOYEE IS 'General information on all corporate employees.';
COMMENT ON COLUMN EMPLOYEE.EMPLOYEE_ID IS 'Employee Number: Unique 4-digit identification number assigned to every employee.';
COMMENT ON COLUMN EMPLOYEE.LAST_NAME IS 'Last name of employee. Stored in all caps.';
COMMENT ON COLUMN EMPLOYEE.FIRST_NAME IS 'First name of employee. Stored in all caps.';
COMMENT ON COLUMN EMPLOYEE.MIDDLE_INITIAL IS 'Middle initial (one character only). Stored in all caps.';
COMMENT ON COLUMN EMPLOYEE.JOB_ID IS 'Job identification number. See JOB for descriptions.';
COMMENT ON COLUMN EMPLOYEE.MANAGER_ID IS 'Manager: The employee number of this employee''s manager. If employee has no manager, value should be NULL.';
COMMENT ON COLUMN EMPLOYEE.HIRE_DATE IS 'Date the employee was HIRED, not necessarily the day the employee STARTED.';
COMMENT ON COLUMN EMPLOYEE.SALARY IS 'Monthly salary for the employee in U.S. dollars. Salary grade information can be found in the table SALARY_GRADE.';
COMMENT ON COLUMN EMPLOYEE.COMMISSION IS 'Commission (in U.S. dollars) earned last month. This field should be NULL for non-commissioned employees.';
COMMENT ON COLUMN EMPLOYEE.DEPARTMENT_ID IS 'Employee''s department number. Further department information is available in the table DEPT.';

COMMENT ON TABLE JOB IS 'All valid job categories for personnel information.';
COMMENT ON COLUMN JOB.JOB_ID IS 'Unique 3-digit code for job function and title.';
COMMENT ON COLUMN JOB.FUNCTION IS 'Generic function class of job.';

COMMENT ON TABLE DEPARTMENT IS 'Department information for all U.S. operations.';
COMMENT ON COLUMN DEPARTMENT.DEPARTMENT_ID IS 'Department number: Unique 2-digit department number assigned to each department.';
COMMENT ON COLUMN DEPARTMENT.NAME IS 'Department name: Only the first 14 characters of a department name can be stored in the database. Storing names in all caps is recommended but not required.';
COMMENT ON COLUMN DEPARTMENT.LOCATION_ID IS 'Department location: The code for where the department''s central office is located. (LOCATION)';

COMMENT ON TABLE LOCATION IS 'Location information for all U.S. departments and warehouses.';
COMMENT ON COLUMN LOCATION.LOCATION_ID IS 'Central location corporate location code.';
COMMENT ON COLUMN LOCATION.REGIONAL_GROUP IS 'Metropolitan center closest to location.';

COMMENT ON TABLE SALARY_GRADE IS 'Salary grade levels. Used to classify monthly salary information in U.S. dollars, as found in EMP.SAL.';
COMMENT ON COLUMN SALARY_GRADE.GRADE_ID IS 'Number used to grade salaries. Higher salary grade numbers denote higher salaries.';
COMMENT ON COLUMN SALARY_GRADE.LOWER_BOUND IS 'The lowest monthly salary (rounded to the nearest U.S. dollar) that applies to a particular salary grade. (i.e. the condition ROUND(EMP.SAL) >= LOWER_BOUND must be met.)';
COMMENT ON COLUMN SALARY_GRADE.UPPER_BOUND IS 'The highest monthly salary (rounded to the nearest U.S. dollar) that applies to a particular salary grade. (i.e. the condition ROUND(EMP.SAL) <= UPPER_BOUND must be met.)';

COMMENT ON TABLE PRODUCT IS 'Products in catalog. Pricing information can be found in table PRICE.';
COMMENT ON COLUMN PRODUCT.PRODUCT_ID IS 'Unique 6 digit number assigned to all products. Number generated by sequence PRODUCT_ID.';
COMMENT ON COLUMN PRODUCT.DESCRIPTION IS 'Full product name (up to 30 characters). Storing product names in all caps is recommended but not required.';

COMMENT ON TABLE PRICE IS 'Prices (both standard and minimum) of products. Database tracks both effective dates and expiration dates for prices.';
COMMENT ON COLUMN PRICE.PRODUCT_ID IS 'Product number to which price applies. Product name found in table PRICE.';
COMMENT ON COLUMN PRICE.LIST_PRICE IS 'Undiscounted price (in U.S. dollars).';
COMMENT ON COLUMN PRICE.MIN_PRICE IS 'Lowest price (in U.S. dollars) that can be offered.';
COMMENT ON COLUMN PRICE.START_DATE IS 'Date which standard and minimum prices go into effect.';
COMMENT ON COLUMN PRICE.END_DATE IS 'Date which standard and minimum price expire. This value can be left NULL.';

COMMENT ON TABLE CUSTOMER IS 'Information on all U.S. customers and prospective customers.';
COMMENT ON COLUMN CUSTOMER.CUSTOMER_ID IS 'Unique 6 digit number assigned to all customers. Number generated by the sequence CUSTOMER_ID.';
COMMENT ON COLUMN CUSTOMER.NAME IS 'Full store name of every customer (up to 45 characters). Storing names in all caps is recommended but not required.';
COMMENT ON COLUMN CUSTOMER.ADDRESS IS 'Street address of customer. CITY, STATE, and ZIP_CODE make up the rest of the address.';
COMMENT ON COLUMN CUSTOMER.CITY IS 'City name for address of customer. Storing city names in all caps is recommended, but not required.';
COMMENT ON COLUMN CUSTOMER.STATE IS 'Two letter abbreviation for state name for address of customer. Must be entered in all caps.';
COMMENT ON COLUMN CUSTOMER.ZIP_CODE IS 'Zip code of customer. Supports both 5 and 9 digit zip codes. Dashes should be omitted from 9 digit zips.';
COMMENT ON COLUMN CUSTOMER.AREA_CODE IS 'Area code of phone number for customer.';
COMMENT ON COLUMN CUSTOMER.PHONE_NUMBER IS 'Phone number of customer. This field does not supports letters and spaces for mnemonics (7 numeric digits only).';
COMMENT ON COLUMN CUSTOMER.SALESPERSON_ID IS 'Employee number of sales representative handling customer''s account. Employee information can be found in the EMPLOYEE table.';
COMMENT ON COLUMN CUSTOMER.CREDIT_LIMIT IS 'Credit limit of customer or prospective customer in U.S. dollars.';
COMMENT ON COLUMN CUSTOMER.COMMENTS IS 'Field can be used flexibly. Suggestions for use include lists of action items or special considerations when dealing with the particular customer.';

COMMENT ON TABLE SALES_ORDER IS 'Sales order for a customer. Items in the order can be found in the table ITEM.';
COMMENT ON COLUMN SALES_ORDER.ORDER_ID IS 'Unique 4 digit number assigned to all orders. Number generated by sequence ORDER_ID.';
COMMENT ON COLUMN SALES_ORDER.ORDER_DATE IS 'Date sales order logged (not necessarily when order was fulfilled.)';
COMMENT ON COLUMN SALES_ORDER.CUSTOMER_ID IS 'Customer ordering the goods. Further information can be found in the table CUSTOMER.';
COMMENT ON COLUMN SALES_ORDER.SHIP_DATE IS 'Date order fulfilled and invoice sent (not necessarily date payment received).';
COMMENT ON COLUMN SALES_ORDER.TOTAL IS 'Total of all items totals in the order. (i.e. the value of TOTAL for order N should contain: SELECT SUM(TOTAL) FROM ITEM WHERE ORDER_ID=N)';

COMMENT ON TABLE ITEM IS 'Items listed in sales orders. Product information can be found in table PRODUCT. Sales order information can be found in table SALES_ORDER.';
COMMENT ON COLUMN ITEM.ORDER_ID IS 'Sales order in which this item appears. Sales order information can be found in table SALES_ORDER.';
COMMENT ON COLUMN ITEM.ITEM_ID IS 'Number of item within the sales order.';
COMMENT ON COLUMN ITEM.PRODUCT_ID IS 'Number of product (described in PRODUCT) that pertains to the line item.';
COMMENT ON COLUMN ITEM.ACTUAL_PRICE IS 'Price to be paid by the customer in U.S. dollars. The standard price is PRICE.STANDARD_PRICE, but in some instances, discounts are given to as low as PRICE.MINIMUM_PRICE.';
COMMENT ON COLUMN ITEM.QUANTITY IS 'The amount of product to be ordered in this line item.';
COMMENT ON COLUMN ITEM.TOTAL IS 'The total price (in U.S. dollars) of this line item. TOTAL = ACTUAL_PRICE * QUANTITY.';

COMMENT ON TABLE SALES IS 'Summary view joining tables SALES_ORDER, ITEM, CUSTOMER, and PRODUCT. Summarizes the item total grouped by sales representative, customer, and product. Used by the SALES ORDERS application.';
COMMENT ON COLUMN SALES.SALESPERSON_ID IS 'Employee number of sales representative handling customer''s account. Employee information can be found in the EMPLOYEE table. (Same as CUSTOMER.SALESPERSON_ID)';
COMMENT ON COLUMN SALES.CUSTOMER_ID IS 'Unique 6 digit number assigned to all customers. Number generated by the sequence CUSTOMER_ID. (Same as CUSTOMER.CUSTOMER_ID)';
COMMENT ON COLUMN SALES.CUSTOMER IS 'Full store name of every customer (up to 45 characters). Storing names in all caps is recommended but not required. (Same as CUSTOMER.NAME)';
COMMENT ON COLUMN SALES.PRODUCT_ID IS 'Unique 6 digit number assigned to all products. Number generated by sequence PRODUCT_ID. (Same as PRODUCT.PRODUCT_ID)';
COMMENT ON COLUMN SALES.PRODUCT IS 'Full product name (up to 30 characters). Storing product names in all caps is recommended but not required. (Same as PRODUCT.PRODUCT_NAME)';
COMMENT ON COLUMN SALES.AMOUNT IS 'Total sales in (U.S. dollars) of the product sold by the representative to the customer.';

REM Insert Data

INSERT INTO LOCATION VALUES (122, 'NEW YORK');
INSERT INTO LOCATION VALUES (124, 'DALLAS');
INSERT INTO LOCATION VALUES (123, 'CHICAGO');
INSERT INTO LOCATION VALUES (167, 'BOSTON');

INSERT INTO DEPARTMENT VALUES (10,'ACCOUNTING','122');
INSERT INTO DEPARTMENT VALUES (20,'RESEARCH','124');
INSERT INTO DEPARTMENT VALUES (30,'SALES','123');
INSERT INTO DEPARTMENT VALUES (40,'OPERATIONS','167');

INSERT INTO DEPARTMENT VALUES
( 12 , 'RESEARCH', '122');

INSERT INTO DEPARTMENT VALUES
( 13 , 'SALES', '122');

INSERT INTO DEPARTMENT VALUES
( 14 , 'OPERATIONS', '122');

INSERT INTO DEPARTMENT VALUES
( 23 , 'SALES', '124');

INSERT INTO DEPARTMENT VALUES
( 24 , 'OPERATIONS', '124');

INSERT INTO DEPARTMENT VALUES
( 34 , 'OPERATIONS', '123');

INSERT INTO DEPARTMENT VALUES
( 43 , 'SALES', '167');

INSERT INTO JOB VALUES (667, 'CLERK');
INSERT INTO JOB VALUES (668, 'STAFF');
INSERT INTO JOB VALUES (669, 'ANALYST');
INSERT INTO JOB VALUES (670, 'SALESPERSON');
INSERT INTO JOB VALUES (671, 'MANAGER');
INSERT INTO JOB VALUES (672, 'PRESIDENT');

INSERT INTO SALARY_GRADE VALUES (1,700,1200);
INSERT INTO SALARY_GRADE VALUES (2,1201,1400);
INSERT INTO SALARY_GRADE VALUES (3,1401,2000);
INSERT INTO SALARY_GRADE VALUES (4,2001,3000);
INSERT INTO SALARY_GRADE VALUES (5,3001,9999);

INSERT INTO EMPLOYEE VALUES
    (7369,'SMITH','JOHN','Q',667,7902,TO_DATE(2446052,'J'),800,NULL,20);
INSERT INTO EMPLOYEE VALUES
    (7499,'ALLEN','KEVIN','J',670,7698,TO_DATE(2446117,'J'),1600,300,30);
INSERT INTO EMPLOYEE VALUES
    (7505,'DOYLE','JEAN','K',671,7839,TO_DATE(2446160,'J'),2850,NULL,13);
INSERT INTO EMPLOYEE VALUES
    (7506,'DENNIS','LYNN','S',671,7839,TO_DATE(2446201,'J'),2750,NULL,23);
INSERT INTO EMPLOYEE VALUES
    (7507,'BAKER','LESLIE','D',671,7839,TO_DATE(2446227,'J'),2200,NULL,14);
INSERT INTO EMPLOYEE VALUES
    (7521,'WARD','CYNTHIA','D',670,7698,TO_DATE(2446119,'J'),1250,500,30);
INSERT INTO EMPLOYEE VALUES
    (7555,'PETERS','DANIEL','T',670,7505,TO_DATE(2446156,'J'),1250,300,13);
INSERT INTO EMPLOYEE VALUES
    (7557,'SHAW','KAREN','P',670,7505,TO_DATE(2446158,'J'),1250,1200,13);
INSERT INTO EMPLOYEE VALUES
    (7560,'DUNCAN','SARAH','S',670,7506,TO_DATE(2446217,'J'),1250,NULL,23);
INSERT INTO EMPLOYEE VALUES
    (7564,'LANGE','GREGORY','J',670,7506,TO_DATE(2446218,'J'),1250,300,23);
INSERT INTO EMPLOYEE VALUES
    (7566,'JONES','TERRY','M',671,7839,TO_DATE(2446158,'J'),2975,NULL,20);
INSERT INTO EMPLOYEE VALUES
    (7569,'ALBERTS','CHRIS','L',671,7839,TO_DATE(2446162,'J'),3000,NULL,12);
INSERT INTO EMPLOYEE VALUES
    (7600,'PORTER','RAYMOND','Y',670,7505,TO_DATE(2446171,'J'),1250,900,13);
INSERT INTO EMPLOYEE VALUES
    (7609,'LEWIS','RICHARD','M',668,7507,TO_DATE(2446172,'J'),1800,NULL,24);
INSERT INTO EMPLOYEE VALUES
    (7654,'MARTIN','KENNETH','J',670,7698,TO_DATE(2446337,'J'),1250,1400,30);
INSERT INTO EMPLOYEE VALUES
    (7676,'SOMMERS','DENISE','D',668,7507,TO_DATE(2446175,'J'),1850,NULL,34);
INSERT INTO EMPLOYEE VALUES
    (7698,'BLAKE','MARION','S',671,7839,TO_DATE(2446187,'J'),2850,NULL,30);
INSERT INTO EMPLOYEE VALUES
    (7782,'CLARK','CAROL','F',671,7839,TO_DATE(2446226,'J'),2450,NULL,10);
INSERT INTO EMPLOYEE VALUES
    (7788,'SCOTT','DONALD','T',669,7566,TO_DATE(2446774,'J'),3000,NULL,20);
INSERT INTO EMPLOYEE VALUES
    (7789,'WEST','LIVIA','N',670,7506,TO_DATE(2446160,'J'),1500,1000,23);
INSERT INTO EMPLOYEE VALUES
    (7799,'FISHER','MATTHEW','G',669,7569,TO_DATE(2446777,'J'),3000,NULL,12);
INSERT INTO EMPLOYEE VALUES
    (7820,'ROSS','PAUL','S',670,7505,TO_DATE(2446218,'J'),1300,800,43);
INSERT INTO EMPLOYEE VALUES
    (7839,'KING','FRANCIS','A',672,NULL,TO_DATE(2446387,'J'),5000,NULL,10);
INSERT INTO EMPLOYEE VALUES
    (7844,'TURNER','MARY','A',670,7698,TO_DATE(2446317,'J'),1500,0,30);
INSERT INTO EMPLOYEE VALUES
    (7876,'ADAMS','DIANE','G',667,7788,TO_DATE(2446808,'J'),1100,NULL,20);
INSERT INTO EMPLOYEE VALUES
    (7900,'JAMES','FRED','S',667,7698,TO_DATE(2446403,'J'),950,NULL,30);
INSERT INTO EMPLOYEE VALUES
    (7902,'FORD','JENNIFER','D',669,7566,TO_DATE(2446403,'J'),3000,NULL,20);
INSERT INTO EMPLOYEE VALUES
    (7916,'ROBERTS','GRACE','M',669,7569,TO_DATE(2446800,'J'),2875,NULL,12);
INSERT INTO EMPLOYEE VALUES
    (7919,'DOUGLAS','MICHAEL','A',667,7799,TO_DATE(2446800,'J'),800,NULL,12);
INSERT INTO EMPLOYEE VALUES
    (7934,'MILLER','BARBARA','M',667,7782,TO_DATE(2446454,'J'),1300,NULL,10);
INSERT INTO EMPLOYEE VALUES
    (7950,'JENSEN','ALICE','B',667,7505,TO_DATE(2446811,'J'),750,NULL,13);
INSERT INTO EMPLOYEE VALUES
    (7954,'MURRAY','JAMES','T',667,7506,TO_DATE(2446812,'J'),750,NULL,23);

INSERT INTO PRICE VALUES
   (100871,4.8,3.2,TO_DATE(2447528,'J'),TO_DATE(2447862,'J'));
INSERT INTO PRICE VALUES
   (100890,58,46.4,TO_DATE(2447528,'J'),NULL);
INSERT INTO PRICE VALUES
   (100890,54,40.5,TO_DATE(2447314,'J'),TO_DATE(2447527,'J'));
INSERT INTO PRICE VALUES
   (100860,35,28,TO_DATE(2448044,'J'),NULL);
INSERT INTO PRICE VALUES
   (100860,32,25.6,TO_DATE(2447893,'J'),TO_DATE(2448043,'J'));
INSERT INTO PRICE VALUES
   (100860,30,24,TO_DATE(2447528,'J'),TO_DATE(2447892,'J'));
INSERT INTO PRICE VALUES
   (100861,45,36,TO_DATE(2448044,'J'),NULL);
INSERT INTO PRICE VALUES
   (100861,42,33.6,TO_DATE(2447893,'J'),TO_DATE(2448043,'J'));
INSERT INTO PRICE VALUES
   (100861,39,31.2,TO_DATE(2447528,'J'),TO_DATE(2447892,'J'));
INSERT INTO PRICE VALUES
   (100870,2.8,2.4,TO_DATE(2447893,'J'),NULL);
INSERT INTO PRICE VALUES
   (100870,2.4,1.9,TO_DATE(2447528,'J'),TO_DATE(2447862,'J'));
INSERT INTO PRICE VALUES
   (100871,5.6,4.8,TO_DATE(2447893,'J'),NULL);
INSERT INTO PRICE VALUES
   (101860,24,18,TO_DATE(2447573,'J'),NULL);
INSERT INTO PRICE VALUES
   (101863,12.5,9.4,TO_DATE(2447573,'J'),NULL);
INSERT INTO PRICE VALUES
   (102130,3.4,2.8,TO_DATE(2447757,'J'),NULL);
INSERT INTO PRICE VALUES
   (200376,2.4,1.75,TO_DATE(2448211,'J'),NULL);
INSERT INTO PRICE VALUES
   (200380,4,3.2,TO_DATE(2448211,'J'),NULL);
INSERT INTO PRICE VALUES
   (103120,23.2,18.6,TO_DATE(2447528,'J'),TO_DATE(2447892,'J'));
INSERT INTO PRICE VALUES
   (103120,24,19.2,TO_DATE(2447893,'J'),TO_DATE(2448043,'J'));
INSERT INTO PRICE VALUES
   (103120,25,20,TO_DATE(2448044,'J'),NULL);
INSERT INTO PRICE VALUES
   (103121,27.8,22.3,TO_DATE(2447528,'J'),TO_DATE(2447892,'J'));
INSERT INTO PRICE VALUES
   (103121,28.8,23,TO_DATE(2447893,'J'),TO_DATE(2448043,'J'));
INSERT INTO PRICE VALUES
   (103121,30,24,TO_DATE(2448044,'J'),NULL);
INSERT INTO PRICE VALUES
   (103130,4,3.5,TO_DATE(2447528,'J'),TO_DATE(2447892,'J'));
INSERT INTO PRICE VALUES
   (103130,4.2,3.6,TO_DATE(2447893,'J'),NULL);
INSERT INTO PRICE VALUES
   (103131,4.2,3.6,TO_DATE(2447528,'J'),TO_DATE(2447892,'J'));
INSERT INTO PRICE VALUES
   (103131,4.5,3.9,TO_DATE(2447893,'J'),NULL);
INSERT INTO PRICE VALUES
   (103140,20,15,TO_DATE(2447573,'J'),NULL);
INSERT INTO PRICE VALUES
   (103141,20,15,TO_DATE(2447573,'J'),NULL);
INSERT INTO PRICE VALUES
   (102132,3.4,2.8,TO_DATE(2447757,'J'),NULL);
INSERT INTO PRICE VALUES
   (104350,40,32,TO_DATE(2447528,'J'),TO_DATE(2447892,'J'));
INSERT INTO PRICE VALUES
   (104350,42,33.6,TO_DATE(2447893,'J'),TO_DATE(2448043,'J'));
INSERT INTO PRICE VALUES
   (104350,44,35.3,TO_DATE(2448044,'J'),NULL);
INSERT INTO PRICE VALUES
   (104351,23.6,18.9,TO_DATE(2447528,'J'),TO_DATE(2447892,'J'));
INSERT INTO PRICE VALUES
   (104351,24.8,19.8,TO_DATE(2447893,'J'),TO_DATE(2448043,'J'));
INSERT INTO PRICE VALUES
   (104351,26,20.8,TO_DATE(2448044,'J'),NULL);
INSERT INTO PRICE VALUES
   (104352,50,35,TO_DATE(2447528,'J'),TO_DATE(2447892,'J'));
INSERT INTO PRICE VALUES
   (104352,54,37.8,TO_DATE(2447893,'J'),TO_DATE(2448043,'J'));
INSERT INTO PRICE VALUES
   (104352,58.3,40.8,TO_DATE(2448044,'J'),NULL);
INSERT INTO PRICE VALUES
   (104360,36,29.5,TO_DATE(2447528,'J'),TO_DATE(2447892,'J'));
INSERT INTO PRICE VALUES
   (104360,39,32,TO_DATE(2447893,'J'),NULL);
INSERT INTO PRICE VALUES
   (104361,47,33,TO_DATE(2447528,'J'),TO_DATE(2447892,'J'));
INSERT INTO PRICE VALUES
   (104361,50,35,TO_DATE(2447893,'J'),NULL);
INSERT INTO PRICE VALUES
   (104362,5,4,TO_DATE(2447573,'J'),NULL);
INSERT INTO PRICE VALUES
   (102134,3.4,2.8,TO_DATE(2447757,'J'),NULL);
INSERT INTO PRICE VALUES
   (105123,36.3,29,TO_DATE(2447528,'J'),TO_DATE(2447892,'J'));
INSERT INTO PRICE VALUES
   (105123,37.7,32,TO_DATE(2447893,'J'),TO_DATE(2448043,'J'));
INSERT INTO PRICE VALUES
   (105123,40,34,TO_DATE(2448044,'J'),NULL);
INSERT INTO PRICE VALUES
   (105124,10,7.6,TO_DATE(2447528,'J'),TO_DATE(2447892,'J'));
INSERT INTO PRICE VALUES
   (105124,12,9,TO_DATE(2447893,'J'),TO_DATE(2448043,'J'));
INSERT INTO PRICE VALUES
   (105124,15,10,TO_DATE(2448044,'J'),NULL);
INSERT INTO PRICE VALUES
   (105125,2,1.7,TO_DATE(2447528,'J'),TO_DATE(2447892,'J'));
INSERT INTO PRICE VALUES
   (105125,3,2.6,TO_DATE(2447893,'J'),NULL);
INSERT INTO PRICE VALUES
   (105126,5,4.3,TO_DATE(2447528,'J'),TO_DATE(2447892,'J'));
INSERT INTO PRICE VALUES
   (105126,6,5,TO_DATE(2447893,'J'),NULL);
INSERT INTO PRICE VALUES
   (105127,6,5,TO_DATE(2448211,'J'),NULL);
INSERT INTO PRICE VALUES
   (105128,10,8,TO_DATE(2448211,'J'),NULL);
INSERT INTO PRICE VALUES
   (102136,3.4,2.8,TO_DATE(2447757,'J'),NULL);

INSERT INTO PRODUCT VALUES ('100860', 'ACE TENNIS RACKET I');
INSERT INTO PRODUCT VALUES ('100861', 'ACE TENNIS RACKET II');
INSERT INTO PRODUCT VALUES ('100870', 'ACE TENNIS BALLS-3 PACK');
INSERT INTO PRODUCT VALUES ('100871', 'ACE TENNIS BALLS-6 PACK');
INSERT INTO PRODUCT VALUES ('100890', 'ACE TENNIS NET');
INSERT INTO PRODUCT VALUES ('101860', 'SP TENNIS RACKET');
INSERT INTO PRODUCT VALUES ('101863', 'SP JUNIOR RACKET');
INSERT INTO PRODUCT VALUES ('102130', 'RH: "GUIDE TO TENNIS"');
INSERT INTO PRODUCT VALUES ('200376', 'SB ENERGY BAR-6 PACK');
INSERT INTO PRODUCT VALUES ('200380', 'SB VITA SNACK-6 PACK');
INSERT INTO PRODUCT VALUES ( 103120 , 'WIFF SOFTBALL BAT I');
INSERT INTO PRODUCT VALUES ( 103121 , 'WIFF SOFTBALL BAT II');
INSERT INTO PRODUCT VALUES ( 103130 , 'WIFF SOFTBALL, SMALL');
INSERT INTO PRODUCT VALUES ( 103131 , 'WIFF SOFTBALL, LARGE');
INSERT INTO PRODUCT VALUES ( 103140 , 'WIFF SOFTBALL MITT (LH)');
INSERT INTO PRODUCT VALUES ( 103141 , 'WIFF SOFTBALL MITT (RH)');
INSERT INTO PRODUCT VALUES ( 102132 , 'RH: "GUIDE TO SOFTBALL"');
INSERT INTO PRODUCT VALUES ( 104350 , 'DUNK BASKETBALL INDOOR');
INSERT INTO PRODUCT VALUES ( 104351 , 'DUNK BASKETBALL OUTDOOR');
INSERT INTO PRODUCT VALUES ( 104352 , 'DUNK BASKETBALL PROFESSIONAL');
INSERT INTO PRODUCT VALUES ( 104360 , 'DUNK HOOP');
INSERT INTO PRODUCT VALUES ( 104361 , 'DUNK HOOP W/FIBERGLASS BOARD');
INSERT INTO PRODUCT VALUES ( 104362 , 'DUNK NETS - RAINBOW');
INSERT INTO PRODUCT VALUES ( 102134 , 'RH: "GUIDE TO BASKETBALL"');
INSERT INTO PRODUCT VALUES ( 105123 , 'YELLOW JERSEY BICYCLE HELMET');
INSERT INTO PRODUCT VALUES ( 105124 , 'YELLOW JERSEY BICYCLE GLOVES');
INSERT INTO PRODUCT VALUES ( 105125 , 'YELLOW JERSEY WATER BOTTLE');
INSERT INTO PRODUCT VALUES ( 105126 , 'YELLOW JERSEY BOTTLE CAGE');
INSERT INTO PRODUCT VALUES ( 105127 , 'YELLOW JERSEY FRAME PUMP');
INSERT INTO PRODUCT VALUES ( 105128 , 'YELLOW JERSEY SADDLE PACK');
INSERT INTO PRODUCT VALUES ( 102136 , 'RH: "GUIDE TO CYCLING"');

INSERT INTO SALES_ORDER VALUES
   (610,TO_DATE(2448264,'J'),101,TO_DATE(2448265,'J'),101.4);
INSERT INTO SALES_ORDER VALUES
   (611,TO_DATE(2448268,'J'),102,TO_DATE(2448268,'J'),45);
INSERT INTO SALES_ORDER VALUES
   (612,TO_DATE(2448272,'J'),104,TO_DATE(2448277,'J'),5860);
INSERT INTO SALES_ORDER VALUES
   (601,TO_DATE(2448013,'J'),106,TO_DATE(2448042,'J'),60.8);
INSERT INTO SALES_ORDER VALUES
   (602,TO_DATE(2448048,'J'),102,TO_DATE(2448063,'J'),56);
INSERT INTO SALES_ORDER VALUES
   (600,TO_DATE(2448013,'J'),103,TO_DATE(2448041,'J'),42);
INSERT INTO SALES_ORDER VALUES
   (604,TO_DATE(2448058,'J'),106,TO_DATE(2448073,'J'),642);
INSERT INTO SALES_ORDER VALUES
   (605,TO_DATE(2448087,'J'),106,TO_DATE(2448103,'J'),8374);
INSERT INTO SALES_ORDER VALUES
   (606,TO_DATE(2448087,'J'),100,TO_DATE(2448103,'J'),3.4);
INSERT INTO SALES_ORDER VALUES
   (609,TO_DATE(2448105,'J'),100,TO_DATE(2448119,'J'),102.5);
INSERT INTO SALES_ORDER VALUES
   (607,TO_DATE(2448091,'J'),104,TO_DATE(2448091,'J'),5.6);
INSERT INTO SALES_ORDER VALUES
   (608,TO_DATE(2448098,'J'),104,TO_DATE(2448098,'J'),35.2);
INSERT INTO SALES_ORDER VALUES
   (603,TO_DATE(2448048,'J'),102,TO_DATE(2448048,'J'),224);
INSERT INTO SALES_ORDER VALUES
   (620,TO_DATE(2448328,'J'),100,TO_DATE(2448328,'J'),4450);
INSERT INTO SALES_ORDER VALUES
   (613,TO_DATE(2448289,'J'),108,TO_DATE(2448289,'J'),6400);
INSERT INTO SALES_ORDER VALUES
   (614,TO_DATE(2448289,'J'),102,TO_DATE(2448293,'J'),23940);
INSERT INTO SALES_ORDER VALUES
   (616,TO_DATE(2448291,'J'),103,TO_DATE(2448298,'J'),764);
INSERT INTO SALES_ORDER VALUES
   (619,TO_DATE(2448310,'J'),104,TO_DATE(2448320,'J'),1260);
INSERT INTO SALES_ORDER VALUES
   (617,TO_DATE(2448293,'J'),105,TO_DATE(2448319,'J'),46370);
INSERT INTO SALES_ORDER VALUES
   (615,TO_DATE(2448289,'J'),107,TO_DATE(2448294,'J'),710);
INSERT INTO SALES_ORDER VALUES
   (618,TO_DATE(2448303,'J'),102,TO_DATE(2448322,'J'),3083);
INSERT INTO SALES_ORDER VALUES
   (621,TO_DATE(2448331,'J'),100,TO_DATE(2448348,'J'),730);
INSERT INTO SALES_ORDER VALUES
   (509,TO_DATE(2447682,'J'),226,TO_DATE(2447688,'J'),1174);
INSERT INTO SALES_ORDER VALUES
   (523,TO_DATE(2447936,'J'),226,TO_DATE(2447936,'J'),1165);
INSERT INTO SALES_ORDER VALUES
   (549,TO_DATE(2448253,'J'),226,TO_DATE(2448271,'J'),1620);
INSERT INTO SALES_ORDER VALUES
   (507,TO_DATE(2447673,'J'),228,TO_DATE(2447674,'J'),886);
INSERT INTO SALES_ORDER VALUES
   (516,TO_DATE(2447800,'J'),228,TO_DATE(2447803,'J'),1815);
INSERT INTO SALES_ORDER VALUES
   (553,TO_DATE(2448290,'J'),228,TO_DATE(2448301,'J'),4400);
INSERT INTO SALES_ORDER VALUES
   (526,TO_DATE(2447955,'J'),221,TO_DATE(2447958,'J'),7700);
INSERT INTO SALES_ORDER VALUES
   (543,TO_DATE(2448139,'J'),221,TO_DATE(2448142,'J'),8400);
INSERT INTO SALES_ORDER VALUES
   (555,TO_DATE(2448320,'J'),221,TO_DATE(2448323,'J'),8540);
INSERT INTO SALES_ORDER VALUES
   (559,TO_DATE(2448212,'J'),222,TO_DATE(2448370,'J'),387.2);
INSERT INTO SALES_ORDER VALUES
   (528,TO_DATE(2447975,'J'),224,TO_DATE(2447975,'J'),3770);
INSERT INTO SALES_ORDER VALUES
   (531,TO_DATE(2447982,'J'),224,TO_DATE(2447984,'J'),1400);
INSERT INTO SALES_ORDER VALUES
   (558,TO_DATE(2448347,'J'),224,TO_DATE(2448349,'J'),1700);
INSERT INTO SALES_ORDER VALUES
   (525,TO_DATE(2447951,'J'),225,TO_DATE(2447954,'J'),377);
INSERT INTO SALES_ORDER VALUES
   (552,TO_DATE(2448290,'J'),223,TO_DATE(2448295,'J'),555.8);
INSERT INTO SALES_ORDER VALUES
   (556,TO_DATE(2448326,'J'),223,TO_DATE(2448328,'J'),85);
INSERT INTO SALES_ORDER VALUES
   (560,TO_DATE(2448372,'J'),223,TO_DATE(2448372,'J'),72);
INSERT INTO SALES_ORDER VALUES
   (565,TO_DATE(2448409,'J'),227,TO_DATE(2448411,'J'),4900);
INSERT INTO SALES_ORDER VALUES
   (574,TO_DATE(2448540,'J'),201,TO_DATE(2448542,'J'),1685);
INSERT INTO SALES_ORDER VALUES
   (576,TO_DATE(2448543,'J'),201,TO_DATE(2448548,'J'),2058.9);
INSERT INTO SALES_ORDER VALUES
   (503,TO_DATE(2447611,'J'),201,TO_DATE(2447623,'J'),1876);
INSERT INTO SALES_ORDER VALUES
   (518,TO_DATE(2447820,'J'),201,TO_DATE(2447836,'J'),2932.5);
INSERT INTO SALES_ORDER VALUES
   (517,TO_DATE(2447823,'J'),201,TO_DATE(2447838,'J'),784);
INSERT INTO SALES_ORDER VALUES
   (544,TO_DATE(2448146,'J'),202,TO_DATE(2448150,'J'),2358);
INSERT INTO SALES_ORDER VALUES
   (524,TO_DATE(2447945,'J'),202,TO_DATE(2447955,'J'),1979);
INSERT INTO SALES_ORDER VALUES
   (502,TO_DATE(2447568,'J'),202,TO_DATE(2447581,'J'),500);
INSERT INTO SALES_ORDER VALUES
   (539,TO_DATE(2448069,'J'),202,TO_DATE(2448076,'J'),1300);
INSERT INTO SALES_ORDER VALUES
   (511,TO_DATE(2447756,'J'),202,TO_DATE(2447760,'J'),647);
INSERT INTO SALES_ORDER VALUES
   (512,TO_DATE(2447767,'J'),203,TO_DATE(2447783,'J'),428);
INSERT INTO SALES_ORDER VALUES
   (562,TO_DATE(2448381,'J'),203,TO_DATE(2448396,'J'),2044.5);
INSERT INTO SALES_ORDER VALUES
   (529,TO_DATE(2447984,'J'),203,TO_DATE(2447987,'J'),1264.7);
INSERT INTO SALES_ORDER VALUES
   (563,TO_DATE(2448402,'J'),204,TO_DATE(2448403,'J'),889);
INSERT INTO SALES_ORDER VALUES
   (568,TO_DATE(2448451,'J'),204,TO_DATE(2448459,'J'),1217.4);
INSERT INTO SALES_ORDER VALUES
   (504,TO_DATE(2447628,'J'),204,TO_DATE(2447629,'J'),1434.7);
INSERT INTO SALES_ORDER VALUES
   (538,TO_DATE(2448066,'J'),204,TO_DATE(2448069,'J'),741);
INSERT INTO SALES_ORDER VALUES
   (535,TO_DATE(2448040,'J'),204,TO_DATE(2448052,'J'),810);
INSERT INTO SALES_ORDER VALUES
   (578,TO_DATE(2448580,'J'),204,TO_DATE(2448595,'J'),2275.6);
INSERT INTO SALES_ORDER VALUES
   (534,TO_DATE(2448023,'J'),206,TO_DATE(2448027,'J'),420);
INSERT INTO SALES_ORDER VALUES
   (536,TO_DATE(2448033,'J'),206,TO_DATE(2448049,'J'),2135.6);
INSERT INTO SALES_ORDER VALUES
   (572,TO_DATE(2448488,'J'),206,TO_DATE(2448493,'J'),1200.5);
INSERT INTO SALES_ORDER VALUES
   (514,TO_DATE(2447775,'J'),207,TO_DATE(2447786,'J'),1140);
INSERT INTO SALES_ORDER VALUES
   (521,TO_DATE(2447851,'J'),207,TO_DATE(2447854,'J'),896.9);
INSERT INTO SALES_ORDER VALUES
   (551,TO_DATE(2448279,'J'),208,TO_DATE(2448294,'J'),1142);
INSERT INTO SALES_ORDER VALUES
   (513,TO_DATE(2447776,'J'),208,TO_DATE(2447784,'J'),1497);
INSERT INTO SALES_ORDER VALUES
   (508,TO_DATE(2447673,'J'),208,TO_DATE(2447686,'J'),1080);
INSERT INTO SALES_ORDER VALUES
   (515,TO_DATE(2447789,'J'),208,TO_DATE(2447796,'J'),1428.5);
INSERT INTO SALES_ORDER VALUES
   (542,TO_DATE(2448115,'J'),208,TO_DATE(2448127,'J'),2409);
INSERT INTO SALES_ORDER VALUES
   (573,TO_DATE(2448540,'J'),201,TO_DATE(2448542,'J'),1627);
INSERT INTO SALES_ORDER VALUES
   (566,TO_DATE(2448417,'J'),201,TO_DATE(2448420,'J'),3389.2);
INSERT INTO SALES_ORDER VALUES
   (520,TO_DATE(2447847,'J'),201,TO_DATE(2447850,'J'),341.6);
INSERT INTO SALES_ORDER VALUES
   (519,TO_DATE(2447820,'J'),201,TO_DATE(2447836,'J'),955);
INSERT INTO SALES_ORDER VALUES
   (575,TO_DATE(2448534,'J'),201,TO_DATE(2448547,'J'),684);
INSERT INTO SALES_ORDER VALUES
   (547,TO_DATE(2448181,'J'),202,TO_DATE(2448192,'J'),984.4);
INSERT INTO SALES_ORDER VALUES
   (540,TO_DATE(2448088,'J'),202,TO_DATE(2448090,'J'),861.2);
INSERT INTO SALES_ORDER VALUES
   (567,TO_DATE(2448443,'J'),202,TO_DATE(2448445,'J'),200);
INSERT INTO SALES_ORDER VALUES
   (570,TO_DATE(2448455,'J'),202,TO_DATE(2448462,'J'),663.6);
INSERT INTO SALES_ORDER VALUES
   (571,TO_DATE(2448471,'J'),202,TO_DATE(2448481,'J'),1095.6);
INSERT INTO SALES_ORDER VALUES
   (541,TO_DATE(2448112,'J'),203,TO_DATE(2448122,'J'),400);
INSERT INTO SALES_ORDER VALUES
   (532,TO_DATE(2447990,'J'),203,TO_DATE(2448003,'J'),1295.4);
INSERT INTO SALES_ORDER VALUES
   (527,TO_DATE(2447950,'J'),204,TO_DATE(2447965,'J'),3054.4);
INSERT INTO SALES_ORDER VALUES
   (501,TO_DATE(2447533,'J'),204,TO_DATE(2447538,'J'),216);
INSERT INTO SALES_ORDER VALUES
   (564,TO_DATE(2448402,'J'),204,TO_DATE(2448403,'J'),898.8);
INSERT INTO SALES_ORDER VALUES
   (537,TO_DATE(2448057,'J'),204,TO_DATE(2448069,'J'),672.9);
INSERT INTO SALES_ORDER VALUES
   (522,TO_DATE(2447899,'J'),204,TO_DATE(2447902,'J'),2578.8);
INSERT INTO SALES_ORDER VALUES
   (554,TO_DATE(2448306,'J'),204,TO_DATE(2448310,'J'),282.8);
INSERT INTO SALES_ORDER VALUES
   (569,TO_DATE(2448454,'J'),205,TO_DATE(2448461,'J'),2073);
INSERT INTO SALES_ORDER VALUES
   (550,TO_DATE(2448284,'J'),205,TO_DATE(2448295,'J'),2356);
INSERT INTO SALES_ORDER VALUES
   (548,TO_DATE(2448222,'J'),205,TO_DATE(2448238,'J'),666.9);
INSERT INTO SALES_ORDER VALUES
   (505,TO_DATE(2447624,'J'),206,TO_DATE(2447639,'J'),650);
INSERT INTO SALES_ORDER VALUES
   (577,TO_DATE(2448536,'J'),206,TO_DATE(2448550,'J'),1265.75);
INSERT INTO SALES_ORDER VALUES
   (533,TO_DATE(2448010,'J'),206,TO_DATE(2448014,'J'),1122.1);
INSERT INTO SALES_ORDER VALUES
   (561,TO_DATE(2448367,'J'),207,TO_DATE(2448384,'J'),2558.3);
INSERT INTO SALES_ORDER VALUES
   (506,TO_DATE(2447644,'J'),208,TO_DATE(2447657,'J'),2600.4);
INSERT INTO SALES_ORDER VALUES
   (530,TO_DATE(2447985,'J'),208,TO_DATE(2447989,'J'),3026.5);
INSERT INTO SALES_ORDER VALUES
   (545,TO_DATE(2448171,'J'),208,TO_DATE(2448178,'J'),475);
INSERT INTO SALES_ORDER VALUES
   (557,TO_DATE(2448324,'J'),208,TO_DATE(2448328,'J'),2461.8);
INSERT INTO SALES_ORDER VALUES
   (546,TO_DATE(2448182,'J'),208,TO_DATE(2448188,'J'),3663);
INSERT INTO SALES_ORDER VALUES
   (510,TO_DATE(2447726,'J'),208,TO_DATE(2447729,'J'),1336.6);

INSERT INTO ITEM VALUES (600,1,100861,42,1,42);
INSERT INTO ITEM VALUES (610,3,100890,58,1,58);
INSERT INTO ITEM VALUES (611,1,100861,45,1,45);
INSERT INTO ITEM VALUES (612,1,100860,30,100,3000);
INSERT INTO ITEM VALUES (601,1,200376,2.4,12,28.8);
INSERT INTO ITEM VALUES (601,2,100860,32,1,32);
INSERT INTO ITEM VALUES (602,1,100870,2.8,20,56);
INSERT INTO ITEM VALUES (604,1,100890,58,3,174);
INSERT INTO ITEM VALUES (604,2,100861,42,2,84);
INSERT INTO ITEM VALUES (604,3,100860,32,12,384);
INSERT INTO ITEM VALUES (603,1,100860,32,7,224);
INSERT INTO ITEM VALUES (610,1,100860,35,1,35);
INSERT INTO ITEM VALUES (610,2,100870,2.8,3,8.4);
INSERT INTO ITEM VALUES (613,4,200376,2.2,200,440);
INSERT INTO ITEM VALUES (614,1,100860,35,444,15540);
INSERT INTO ITEM VALUES (614,2,100870,2.8,1000,2800);
INSERT INTO ITEM VALUES (612,2,100861,40.5,20,810);
INSERT INTO ITEM VALUES (612,3,101863,10,150,1500);
INSERT INTO ITEM VALUES (620,1,100860,35,10,350);
INSERT INTO ITEM VALUES (620,2,200376,2.4,1000,2400);
INSERT INTO ITEM VALUES (620,3,102130,3.4,500,1700);
INSERT INTO ITEM VALUES (613,1,100871,5.6,100,560);
INSERT INTO ITEM VALUES (613,2,101860,24,200,4800);
INSERT INTO ITEM VALUES (613,3,200380,4,150,600);
INSERT INTO ITEM VALUES (619,3,102130,3.4,100,340);
INSERT INTO ITEM VALUES (617,1,100860,35,50,1750);
INSERT INTO ITEM VALUES (617,2,100861,45,100,4500);
INSERT INTO ITEM VALUES (614,3,100871,5.6,1000,5600);
INSERT INTO ITEM VALUES (616,1,100861,45,10,450);
INSERT INTO ITEM VALUES (616,2,100870,2.8,50,140);
INSERT INTO ITEM VALUES (616,3,100890,58,2,116);
INSERT INTO ITEM VALUES (616,4,102130,3.4,10,34);
INSERT INTO ITEM VALUES (616,5,200376,2.4,10,24);
INSERT INTO ITEM VALUES (619,1,200380,4,100,400);
INSERT INTO ITEM VALUES (619,2,200376,2.4,100,240);
INSERT INTO ITEM VALUES (615,1,100861,45,4,180);
INSERT INTO ITEM VALUES (607,1,100871,5.6,1,5.6);
INSERT INTO ITEM VALUES (615,2,100870,2.8,100,280);
INSERT INTO ITEM VALUES (617,3,100870,2.8,500,1400);
INSERT INTO ITEM VALUES (617,4,100871,5.6,500,2800);
INSERT INTO ITEM VALUES (617,5,100890,58,500,29000);
INSERT INTO ITEM VALUES (617,6,101860,24,100,2400);
INSERT INTO ITEM VALUES (617,7,101863,12.5,200,2500);
INSERT INTO ITEM VALUES (617,8,102130,3.4,100,340);
INSERT INTO ITEM VALUES (617,9,200376,2.4,200,480);
INSERT INTO ITEM VALUES (617,10,200380,4,300,1200);
INSERT INTO ITEM VALUES (609,2,100870,2.5,5,12.5);
INSERT INTO ITEM VALUES (609,3,100890,50,1,50);
INSERT INTO ITEM VALUES (618,1,100860,35,23,805);
INSERT INTO ITEM VALUES (618,2,100861,45,50,2250);
INSERT INTO ITEM VALUES (618,3,100870,2.8,10,28);
INSERT INTO ITEM VALUES (621,1,100861,45,10,450);
INSERT INTO ITEM VALUES (621,2,100870,2.8,100,280);
INSERT INTO ITEM VALUES (615,3,100871,5,50,250);
INSERT INTO ITEM VALUES (608,1,101860,24,1,24);
INSERT INTO ITEM VALUES (608,2,100871,5.6,2,11.2);
INSERT INTO ITEM VALUES (609,1,100861,40,1,40);
INSERT INTO ITEM VALUES (606,1,102130,3.4,1,3.4);
INSERT INTO ITEM VALUES (605,1,100861,45,100,4500);
INSERT INTO ITEM VALUES (605,2,100870,2.8,500,1400);
INSERT INTO ITEM VALUES (605,3,100890,58,5,290);
INSERT INTO ITEM VALUES (605,4,101860,24,50,1200);
INSERT INTO ITEM VALUES (605,5,101863,9.5,100,950);
INSERT INTO ITEM VALUES (605,6,102130,3.4,10,34);
INSERT INTO ITEM VALUES (612,4,100871,5.5,100,550);
INSERT INTO ITEM VALUES (619,4,100871,5.6,50,280);
INSERT INTO ITEM VALUES (509,1,105123,35,30,1050);
INSERT INTO ITEM VALUES (509,2,105124,9,10,90);
INSERT INTO ITEM VALUES (509,3,102136,3.4,10,34);
INSERT INTO ITEM VALUES (523,1,102136,3.4,10,34);
INSERT INTO ITEM VALUES (523,2,105123,37.7,30,1131);
INSERT INTO ITEM VALUES (549,1,105123,38,5,190);
INSERT INTO ITEM VALUES (549,2,105127,6,30,180);
INSERT INTO ITEM VALUES (549,3,105125,3,200,600);
INSERT INTO ITEM VALUES (549,4,105124,13,50,650);
INSERT INTO ITEM VALUES (507,1,105123,35.3,20,706);
INSERT INTO ITEM VALUES (507,2,105124,9,20,180);
INSERT INTO ITEM VALUES (516,1,105124,9,60,540);
INSERT INTO ITEM VALUES (516,2,102136,3.2,250,800);
INSERT INTO ITEM VALUES (516,3,105125,1.9,250,475);
INSERT INTO ITEM VALUES (553,1,105127,6,500,3000);
INSERT INTO ITEM VALUES (553,2,200376,1.75,800,1400);
INSERT INTO ITEM VALUES (526,1,105123,32,100,3200);
INSERT INTO ITEM VALUES (526,2,105124,9,500,4500);
INSERT INTO ITEM VALUES (543,1,105123,34,100,3400);
INSERT INTO ITEM VALUES (543,2,105124,10,500,5000);
INSERT INTO ITEM VALUES (555,1,105123,34,100,3400);
INSERT INTO ITEM VALUES (555,2,105124,10,500,5000);
INSERT INTO ITEM VALUES (555,3,102136,2.8,50,140);
INSERT INTO ITEM VALUES (559,1,105123,40,5,200);
INSERT INTO ITEM VALUES (559,2,105124,15,5,75);
INSERT INTO ITEM VALUES (559,3,105127,6,5,30);
INSERT INTO ITEM VALUES (559,4,102136,3.4,3,10.2);
INSERT INTO ITEM VALUES (559,5,200376,2.4,30,72);
INSERT INTO ITEM VALUES (528,1,105123,37.7,100,3770);
INSERT INTO ITEM VALUES (531,1,105124,11,100,1100);
INSERT INTO ITEM VALUES (531,2,102136,3,100,300);
INSERT INTO ITEM VALUES (558,1,105124,14,100,1400);
INSERT INTO ITEM VALUES (558,2,102136,3,100,300);
INSERT INTO ITEM VALUES (525,1,105123,37.7,10,377);
INSERT INTO ITEM VALUES (552,1,105123,40,10,400);
INSERT INTO ITEM VALUES (552,2,105124,15,10,150);
INSERT INTO ITEM VALUES (552,3,102136,3.4,1,3.4);
INSERT INTO ITEM VALUES (552,4,200376,2.4,1,2.4);
INSERT INTO ITEM VALUES (556,1,102136,3.4,25,85);
INSERT INTO ITEM VALUES (560,1,200376,2.4,30,72);
INSERT INTO ITEM VALUES (565,1,105123,37,100,3700);
INSERT INTO ITEM VALUES (565,2,105124,12,100,1200);
INSERT INTO ITEM VALUES (574,1,104350,41.8,25,1045);
INSERT INTO ITEM VALUES (574,2,200380,3.2,200,640);
INSERT INTO ITEM VALUES (576,1,104350,44,10,440);
INSERT INTO ITEM VALUES (576,2,104351,26,10,260);
INSERT INTO ITEM VALUES (576,3,104362,4.25,170,722.5);
INSERT INTO ITEM VALUES (576,4,200376,2.16,90,194.4);
INSERT INTO ITEM VALUES (576,5,200380,3.4,130,442);
INSERT INTO ITEM VALUES (503,1,104350,38,30,1140);
INSERT INTO ITEM VALUES (503,2,104351,23.6,10,236);
INSERT INTO ITEM VALUES (503,3,104352,50,10,500);
INSERT INTO ITEM VALUES (518,1,104350,38,25,950);
INSERT INTO ITEM VALUES (518,2,104351,23.6,20,472);
INSERT INTO ITEM VALUES (518,3,104360,36,20,720);
INSERT INTO ITEM VALUES (518,4,104362,4.25,170,722.5);
INSERT INTO ITEM VALUES (518,5,102134,3.4,20,68);
INSERT INTO ITEM VALUES (517,1,104352,50,15,750);
INSERT INTO ITEM VALUES (517,2,102134,3.4,10,34);
INSERT INTO ITEM VALUES (544,1,104350,44,15,660);
INSERT INTO ITEM VALUES (544,2,104351,26,15,390);
INSERT INTO ITEM VALUES (544,3,104352,58.3,10,583);
INSERT INTO ITEM VALUES (544,4,104361,50,10,500);
INSERT INTO ITEM VALUES (544,5,104362,4.5,50,225);
INSERT INTO ITEM VALUES (524,1,104350,42,10,420);
INSERT INTO ITEM VALUES (524,2,104351,23.56,35,824.6);
INSERT INTO ITEM VALUES (524,3,104362,4.25,150,637.5);
INSERT INTO ITEM VALUES (524,4,102134,3.23,30,96.9);
INSERT INTO ITEM VALUES (502,1,104352,50,10,500);
INSERT INTO ITEM VALUES (539,1,104351,26,20,520);
INSERT INTO ITEM VALUES (539,2,104360,39,20,780);
INSERT INTO ITEM VALUES (511,1,104351,23.6,15,354);
INSERT INTO ITEM VALUES (511,2,104362,4.5,50,225);
INSERT INTO ITEM VALUES (511,3,102134,3.4,20,68);
INSERT INTO ITEM VALUES (512,1,104360,36,10,360);
INSERT INTO ITEM VALUES (512,2,102134,3.4,20,68);
INSERT INTO ITEM VALUES (562,1,104351,24.7,25,617.5);
INSERT INTO ITEM VALUES (562,2,104352,58.3,15,874.5);
INSERT INTO ITEM VALUES (562,3,104362,4.25,130,552.5);
INSERT INTO ITEM VALUES (529,1,104350,39.89,30,1196.7);
INSERT INTO ITEM VALUES (529,2,102134,3.4,20,68);
INSERT INTO ITEM VALUES (563,1,104352,58.3,10,583);
INSERT INTO ITEM VALUES (563,2,200376,2.04,150,306);
INSERT INTO ITEM VALUES (568,1,104351,24.7,30,741);
INSERT INTO ITEM VALUES (568,2,200376,2.04,110,224.4);
INSERT INTO ITEM VALUES (568,3,200380,3.6,70,252);
INSERT INTO ITEM VALUES (504,1,104350,40,10,400);
INSERT INTO ITEM VALUES (504,2,104351,22.42,35,784.7);
INSERT INTO ITEM VALUES (504,3,104352,50,5,250);
INSERT INTO ITEM VALUES (538,1,104351,24.7,30,741);
INSERT INTO ITEM VALUES (535,1,104352,54,15,810);
INSERT INTO ITEM VALUES (578,1,104350,41.8,40,1672);
INSERT INTO ITEM VALUES (578,2,200376,2.04,190,387.6);
INSERT INTO ITEM VALUES (578,3,200380,3.6,60,216);
INSERT INTO ITEM VALUES (534,1,104350,42,10,420);
INSERT INTO ITEM VALUES (536,1,104350,39.89,40,1595.6);
INSERT INTO ITEM VALUES (536,2,104352,54,10,540);
INSERT INTO ITEM VALUES (572,1,104351,24.7,25,617.5);
INSERT INTO ITEM VALUES (572,2,104352,58.3,10,583);
INSERT INTO ITEM VALUES (514,1,104350,38,30,1140);
INSERT INTO ITEM VALUES (521,1,104350,40,20,800);
INSERT INTO ITEM VALUES (521,2,102134,3.23,30,96.9);
INSERT INTO ITEM VALUES (551,1,104350,44,10,440);
INSERT INTO ITEM VALUES (551,2,104361,50,5,250);
INSERT INTO ITEM VALUES (551,3,102134,3.4,20,68);
INSERT INTO ITEM VALUES (551,4,200376,1.92,200,384);
INSERT INTO ITEM VALUES (513,1,104350,40,15,600);
INSERT INTO ITEM VALUES (513,2,104351,23.6,20,472);
INSERT INTO ITEM VALUES (513,3,104362,4.25,100,425);
INSERT INTO ITEM VALUES (508,1,104360,36,20,720);
INSERT INTO ITEM VALUES (508,2,104362,4.5,80,360);
INSERT INTO ITEM VALUES (515,1,104350,40,20,800);
INSERT INTO ITEM VALUES (515,2,104351,22.42,25,560.5);
INSERT INTO ITEM VALUES (515,3,102134,3.4,20,68);
INSERT INTO ITEM VALUES (542,1,104350,41.8,25,1045);
INSERT INTO ITEM VALUES (542,2,104360,39,20,780);
INSERT INTO ITEM VALUES (542,3,104361,50,11,550);
INSERT INTO ITEM VALUES (542,4,102134,3.4,10,34);
INSERT INTO ITEM VALUES (573,1,103120,22.5,50,1125);
INSERT INTO ITEM VALUES (573,2,103131,4.04,50,202);
INSERT INTO ITEM VALUES (573,3,103141,20,15,300);
INSERT INTO ITEM VALUES (566,1,103120,23.75,40,950);
INSERT INTO ITEM VALUES (566,2,103121,27,50,1350);
INSERT INTO ITEM VALUES (566,3,103130,3.77,60,226.2);
INSERT INTO ITEM VALUES (566,4,103131,3.9,170,663);
INSERT INTO ITEM VALUES (566,5,103141,20,10,200);
INSERT INTO ITEM VALUES (520,1,103130,4,10,40);
INSERT INTO ITEM VALUES (520,2,103131,3.77,80,301.6);
INSERT INTO ITEM VALUES (519,1,103130,3.5,110,385);
INSERT INTO ITEM VALUES (519,2,103140,19,30,570);
INSERT INTO ITEM VALUES (575,1,103140,19,30,570);
INSERT INTO ITEM VALUES (575,2,200380,3.8,30,114);
INSERT INTO ITEM VALUES (547,1,103130,3.77,80,301.6);
INSERT INTO ITEM VALUES (547,2,103131,4.04,70,282.8);
INSERT INTO ITEM VALUES (547,3,103140,20,15,300);
INSERT INTO ITEM VALUES (547,4,103141,20,5,100);
INSERT INTO ITEM VALUES (540,1,103130,3.98,40,159.2);
INSERT INTO ITEM VALUES (540,2,103131,3.9,180,702);
INSERT INTO ITEM VALUES (567,1,103140,20,10,200);
INSERT INTO ITEM VALUES (570,1,103131,4.04,90,363.6);
INSERT INTO ITEM VALUES (570,2,103140,20,15,300);
INSERT INTO ITEM VALUES (571,1,103131,4.04,70,282.8);
INSERT INTO ITEM VALUES (571,2,103140,19,25,475);
INSERT INTO ITEM VALUES (571,3,102132,3.4,15,51);
INSERT INTO ITEM VALUES (571,4,200376,2.16,80,172.8);
INSERT INTO ITEM VALUES (571,5,200380,3.8,30,114);
INSERT INTO ITEM VALUES (541,1,103140,20,10,200);
INSERT INTO ITEM VALUES (541,2,103141,20,10,200);
INSERT INTO ITEM VALUES (532,1,103120,21.59,60,1295.4);
INSERT INTO ITEM VALUES (527,1,103120,21.59,60,1295.4);
INSERT INTO ITEM VALUES (527,2,103121,27.35,40,1094);
INSERT INTO ITEM VALUES (527,3,103140,19,35,665);
INSERT INTO ITEM VALUES (501,1,103130,3.6,60,216);
INSERT INTO ITEM VALUES (564,1,103130,3.77,80,301.6);
INSERT INTO ITEM VALUES (564,2,103131,3.9,120,468);
INSERT INTO ITEM VALUES (564,3,102132,3.23,40,129.2);
INSERT INTO ITEM VALUES (537,1,103130,3.6,160,576);
INSERT INTO ITEM VALUES (537,2,102132,3.23,30,96.9);
INSERT INTO ITEM VALUES (522,1,103120,21.59,80,1727.2);
INSERT INTO ITEM VALUES (522,2,103121,28.8,10,288);
INSERT INTO ITEM VALUES (522,3,103131,4.04,90,363.6);
INSERT INTO ITEM VALUES (522,4,103141,20,10,200);
INSERT INTO ITEM VALUES (554,1,103131,4.04,70,282.8);
INSERT INTO ITEM VALUES (569,1,103120,22.5,50,1125);
INSERT INTO ITEM VALUES (569,2,103130,3.6,180,648);
INSERT INTO ITEM VALUES (569,3,103141,20,15,300);
INSERT INTO ITEM VALUES (550,1,103120,22.5,60,1350);
INSERT INTO ITEM VALUES (550,2,103140,19,45,855);
INSERT INTO ITEM VALUES (550,3,103141,20,5,100);
INSERT INTO ITEM VALUES (550,4,102132,3.4,15,51);
INSERT INTO ITEM VALUES (548,1,103140,19,30,570);
INSERT INTO ITEM VALUES (548,2,102132,3.23,30,96.9);
INSERT INTO ITEM VALUES (505,1,103130,3.5,100,350);
INSERT INTO ITEM VALUES (505,2,103140,20,15,300);
INSERT INTO ITEM VALUES (577,1,103121,30,20,600);
INSERT INTO ITEM VALUES (577,2,103131,3.9,150,585);
INSERT INTO ITEM VALUES (577,3,102132,3.23,25,80.75);
INSERT INTO ITEM VALUES (533,1,103121,27.35,30,820.5);
INSERT INTO ITEM VALUES (533,2,103130,3.77,80,301.6);
INSERT INTO ITEM VALUES (561,1,103120,22.5,80,1800);
INSERT INTO ITEM VALUES (561,2,103130,3.77,70,263.9);
INSERT INTO ITEM VALUES (561,3,103141,20,15,300);
INSERT INTO ITEM VALUES (561,4,200376,2.16,90,194.4);
INSERT INTO ITEM VALUES (506,1,103120,20.88,80,1670.4);
INSERT INTO ITEM VALUES (506,2,103130,3.5,180,630);
INSERT INTO ITEM VALUES (506,3,103141,20,15,300);
INSERT INTO ITEM VALUES (530,1,103121,25.91,50,1295.5);
INSERT INTO ITEM VALUES (530,2,103130,3.6,200,720);
INSERT INTO ITEM VALUES (530,3,103140,19,40,760);
INSERT INTO ITEM VALUES (530,4,103141,20,10,200);
INSERT INTO ITEM VALUES (530,5,102132,3.4,15,51);
INSERT INTO ITEM VALUES (545,1,103140,19,25,475);
INSERT INTO ITEM VALUES (557,1,103120,23.75,40,950);
INSERT INTO ITEM VALUES (557,2,103121,30,10,300);
INSERT INTO ITEM VALUES (557,3,103140,19,35,665);
INSERT INTO ITEM VALUES (557,4,200376,2.16,80,172.8);
INSERT INTO ITEM VALUES (557,5,200380,3.4,110,374);
INSERT INTO ITEM VALUES (546,1,103120,22.5,50,1125);
INSERT INTO ITEM VALUES (546,2,103121,30,20,600);
INSERT INTO ITEM VALUES (546,3,103130,3.6,120,432);
INSERT INTO ITEM VALUES (546,4,103131,3.9,140,546);
INSERT INTO ITEM VALUES (546,5,103140,19,40,760);
INSERT INTO ITEM VALUES (546,6,103141,20,10,200);
INSERT INTO ITEM VALUES (510,1,103120,20.88,60,1252.8);
INSERT INTO ITEM VALUES (510,2,103131,4.19,20,83.8);

INSERT INTO CUSTOMER
(ZIP_CODE, STATE, SALESPERSON_ID, PHONE_NUMBER,
NAME,
CUSTOMER_ID, CREDIT_LIMIT, CITY, AREA_CODE, ADDRESS,
COMMENTS)
VALUES ('96711', 'CA', '7844', '5986609',
'JOCKSPORTS',
'100', '5000', 'BELMONT', '415', '345 VIEWRIDGE',
'Very friendly people to work with -- sales rep likes to be called Mike.');

INSERT INTO CUSTOMER
(ZIP_CODE, STATE, SALESPERSON_ID, PHONE_NUMBER,
NAME,
CUSTOMER_ID, CREDIT_LIMIT, CITY, AREA_CODE, ADDRESS,
COMMENTS)
VALUES ('94061', 'CA', '7521', '3681223',
'TKB SPORT SHOP',
'101', '10000', 'REDWOOD CITY', '415', '490 BOLI RD.',
'Rep called 5/8 about change in order - contact shipping.');

INSERT INTO CUSTOMER
(ZIP_CODE, STATE, SALESPERSON_ID, PHONE_NUMBER,
NAME,
CUSTOMER_ID, CREDIT_LIMIT, CITY, AREA_CODE, ADDRESS,
COMMENTS)
VALUES ('95133', 'CA', '7654', '6443341',
'VOLLYRITE',
'102', '7000', 'BURLINGAME', '415', '9722 HAMILTON',
'Company doing heavy promotion beginning 10/89. Prepare for large orders during winter.');

INSERT INTO CUSTOMER
(ZIP_CODE, STATE, SALESPERSON_ID, PHONE_NUMBER,
NAME,
CUSTOMER_ID, CREDIT_LIMIT, CITY, AREA_CODE, ADDRESS,
COMMENTS)
VALUES ('97544', 'CA', '7521', '6779312',
'JUST TENNIS',
'103', '3000', 'BURLINGAME', '415', 'HILLVIEW MALL',
'Contact rep about new line of tennis rackets.');

INSERT INTO CUSTOMER
(ZIP_CODE, STATE, SALESPERSON_ID, PHONE_NUMBER,
NAME,
CUSTOMER_ID, CREDIT_LIMIT, CITY, AREA_CODE, ADDRESS,
COMMENTS)
VALUES ('93301', 'CA', '7499', '9962323',
'EVERY MOUNTAIN',
'104', '10000', 'CUPERTINO', '408', '574 SURRY RD.',
'Customer with high market share (23%) due to aggressive advertising.');

INSERT INTO CUSTOMER
(ZIP_CODE, STATE, SALESPERSON_ID, PHONE_NUMBER,
NAME,
CUSTOMER_ID, CREDIT_LIMIT, CITY, AREA_CODE, ADDRESS,
COMMENTS)
VALUES ('91003', 'CA', '7844', '3769966',
'K + T SPORTS',
'105', '5000', 'SANTA CLARA', '408', '3476 EL PASEO',
'Tends to order large amounts of merchandise at once. Accounting is considering raising their credit limit. Usually pays on time.');

INSERT INTO CUSTOMER
(ZIP_CODE, STATE, SALESPERSON_ID, PHONE_NUMBER,
NAME,
CUSTOMER_ID, CREDIT_LIMIT, CITY, AREA_CODE, ADDRESS,
COMMENTS)
VALUES ('94301', 'CA', '7521', '3649777',
'SHAPE UP',
'106', '6000', 'PALO ALTO', '415', '908 SEQUOIA',
'Support intensive. Orders small amounts (< 800) of merchandise at a time.');

INSERT INTO CUSTOMER
(ZIP_CODE, STATE, SALESPERSON_ID, PHONE_NUMBER,
NAME,
CUSTOMER_ID, CREDIT_LIMIT, CITY, AREA_CODE, ADDRESS,
COMMENTS)
VALUES ('93301', 'CA', '7499', '9674398',
'WOMENS SPORTS',
'107', '10000', 'SUNNYVALE', '408', 'VALCO VILLAGE',
'First sporting goods store geared exclusively towards women. Unusual promotional style and very willing to take chances towards new products!');

INSERT INTO CUSTOMER
(ZIP_CODE, STATE, SALESPERSON_ID, PHONE_NUMBER,
NAME,
CUSTOMER_ID, CREDIT_LIMIT, CITY, AREA_CODE, ADDRESS,
COMMENTS)
VALUES ('55649', 'MN', '7844', '5669123',
'NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER',
'108', '8000', 'HIBBING', '612', '98 LONE PINE WAY', '');

INSERT INTO CUSTOMER VALUES
( 201 , 'STADIUM SPORTS', '47 IRVING PL.',
'NEW YORK', 'NY', '10003',
'212', '5555335', 7557 , 10000 ,
'Large general-purpose sports store with an affluent customer base.');

INSERT INTO CUSTOMER VALUES
( 202 , 'HOOPS', '2345 ADAMS AVE.',
'LEICESTER', 'MA', '01524',
'508', '5557542', 7820 , 5000 ,
'Specializes in basketball equipment.');

INSERT INTO CUSTOMER VALUES
( 203 , 'REBOUND SPORTS', '2 E. 14TH ST.',
'NEW YORK', 'NY', '10009',
'212', '5555989', 7557 , 10000 ,
'Follow up on the promotion proposal.');

INSERT INTO CUSTOMER VALUES
( 204 , 'THE POWER FORWARD', '1 KNOTS LANDING',
'DALLAS', 'TX', '75248',
'214', '5550505', 7560 , 12000 ,
'Large floorspace. Prefers maintaining large amounts of inventory on hand.');

INSERT INTO CUSTOMER VALUES
( 205 , 'POINT GUARD', '20 THURSTON ST.',
'YONKERS', 'NY', '10956',
'914', '5554766', 7557 , 3000 ,
'Tremendous potential for an exclusive agreement.');

INSERT INTO CUSTOMER VALUES
( 206 , 'THE COLISEUM', '5678 WILBUR PL.',
'SCARSDALE', 'NY', '10583',
'914', '5550217', 7557 , 6000 ,
'Contact rep. about new product lines.');

INSERT INTO CUSTOMER VALUES
( 207 , 'FAST BREAK', '1000 HERBERT LN.',
'CONCORD', 'MA', '01742',
'508', '5551298', 7820 , 7000 ,
'Customer requires written price quotes before making purchase requisitions.');

INSERT INTO CUSTOMER VALUES
( 208 , 'AL AND BOB''S SPORTS', '260 YORKTOWN CT.',
'AUSTIN', 'TX', '78731',
'512', '5557631', 7560 , 4000 ,
'Very personal purchasing agents -- Sharon and Scott.');

INSERT INTO CUSTOMER VALUES
( 211 , 'AT BAT', '234 BEACHEM ST.',
'BROOKLINE', 'MA', '02146',
'617', '5557385', 7820 , 8000 ,
'Have an open purchase order for $3000. Ship immediately on request.');

INSERT INTO CUSTOMER VALUES
( 212 , 'ALL SPORT', '1000 38TH ST.',
'BROOKLYN', 'NY', '11210',
'718', '5551739', 7600 , 6000 ,
'Pursue a contract -- possible candidate for volume purchasing agreements.');

INSERT INTO CUSTOMER VALUES
( 213 , 'GOOD SPORT', '400 46TH ST.',
'SUNNYSIDE', 'NY', '11104',
'718', '5553771', 7600 , 5000 ,
'May be moving to a larger location.');

INSERT INTO CUSTOMER VALUES
( 214 , 'AL''S PRO SHOP', '45 SPRUCE ST.',
'SPRING', 'TX', '77388',
'713', '5555172', 7564 , 8000 ,
'Target market is serious athletes.');

INSERT INTO CUSTOMER VALUES
( 215 , 'BOB''S FAMILY SPORTS', '400 E. 23RD',
'HOUSTON', 'TX', '77026',
'713', '5558015', 7654 , 8000 ,
'Target market is casual and weekend athletes. Offers a large selection.');

INSERT INTO CUSTOMER VALUES
( 216 , 'THE ALL AMERICAN', '547 PRENTICE RD.',
'CHELSEA', 'MA', '02150',
'617', '5553047', 7820 , 5000 ,
'Customer prefers to be called between 10 and 12.');

INSERT INTO CUSTOMER VALUES
( 217 , 'HIT, THROW, AND RUN', '333 WOOD COURT',
'GRAPEVINE', 'TX', '76051',
'817', '5552352', 7564 , 6000 ,
'General purpose sports store.');

INSERT INTO CUSTOMER VALUES
( 218 , 'THE OUTFIELD', '346 GARDEN BLVD.',
'FLUSHING', 'NY', '11355',
'718', '5552131', 7820 , 4000 ,
'Store does not open until 11am and does not have an answering service.');

INSERT INTO CUSTOMER VALUES
( 221 , 'WHEELS AND DEALS', '2 MEMORIAL DRIVE',
'HOUSTON', 'TX', '77007',
'713', '5554139', 7789 , 10000 ,
'Discount bicycle and sporting good store.');

INSERT INTO CUSTOMER VALUES
( 222 , 'JUST BIKES', '4000 PARKRIDGE BLVD.',
'DALLAS', 'TX', '75205',
'214', '5558735', 7789 , 4000 ,
'Exclusive bicycle dealer.');

INSERT INTO CUSTOMER VALUES
( 223 , 'VELO SPORTS', '23 WHITE ST.',
'MALDEN', 'MA', '02148',
'617', '5554983', 7820 , 5000 ,
'Clerk answers all phone lines. Ask for Mike.');

INSERT INTO CUSTOMER VALUES
( 224 , 'JOE''S BIKE SHOP', '4500 FOX COURT',
'GRAND PRARIE', 'TX', '75051',
'214', '5559834', 7789 , 6000 ,
'Call Joe to make sure last shipment was complete.');

INSERT INTO CUSTOMER VALUES
( 225 , 'BOB''S SWIM, CYCLE, AND RUN', '300 HORSECREEK CIRCLE',
'IRVING', 'TX', '75039',
'214', '5558388', 7789 , 7000 ,
'Store catering to triathletes.');

INSERT INTO CUSTOMER VALUES
( 226 , 'CENTURY SHOP', '8 DAGMAR DR.',
'HUNTINGTON', 'NY', '11743',
'516', '5553006', 7555 , 4000 ,
'Customer in the midst of a cost-cutting program.');

INSERT INTO CUSTOMER VALUES
( 227 , 'THE TOUR', '2500 GARDNER RD.',
'SOMERVILLE', 'MA', '02144',
'617', '5556673', 7820 , 5000 ,
'Customer referred to us by The All American.');

INSERT INTO CUSTOMER VALUES
( 228 , 'FITNESS FIRST', '5000 85TH ST.',
'JACKSON HEIGHTS', 'NY', '11372',
'718', '5558710', 7555 , 4000 ,
'Recently acquired another sporting goods store. Expect higher volume in the future.');

COMMIT;

REM Create indexes

CREATE UNIQUE INDEX I_LOCATION$LOCATION_ID ON LOCATION (LOCATION_ID);
CREATE UNIQUE INDEX I_DEPARTMENT$DEPARTMENT_ID ON DEPARTMENT (DEPARTMENT_ID);
CREATE UNIQUE INDEX I_JOB$JOB_ID ON JOB (JOB_ID);
CREATE UNIQUE INDEX I_EMPLOYEE$EMPLOYEE_ID ON EMPLOYEE (EMPLOYEE_ID);
CREATE UNIQUE INDEX I_SALARY_GRADE$GRADE_ID ON SALARY_GRADE (GRADE_ID);
CREATE UNIQUE INDEX I_PRODUCT$PRODUCT_ID ON PRODUCT (PRODUCT_ID);
CREATE UNIQUE INDEX I_PRICE ON PRICE (PRODUCT_ID, START_DATE);
CREATE UNIQUE INDEX I_CUSTOMER$CUSTOMER_ID ON CUSTOMER (CUSTOMER_ID);
CREATE UNIQUE INDEX I_SALES_ORDER$ORDER_ID ON SALES_ORDER (ORDER_ID);
CREATE UNIQUE INDEX I_ITEM ON ITEM (ORDER_ID, ITEM_ID);

ORACLE API for Lookup Code and Value Creation

2013-12-14T00:27:00.001+05:30

Oracle APIs for Creation of Lookup Code and Values in ORacle Apps....

DECLARE
   ln_rowid    VARCHAR2 (1000);
   ln_rowid1   VARCHAR2 (1000);
BEGIN
   fnd_lookup_types_pkg.insert_row (x_rowid                    => ln_rowid,
                                    x_lookup_type              => 'XXND BUSINESS CYCLE',
                                    x_security_group_id        => 0,
                                    x_view_application_id      => 3,
                                    x_application_id           => 20087,
                                    x_customization_level      => 'U',
                                    x_meaning                  => 'XXND BUSINESS CYCLE',
                                    x_description              => 'XXND BUSINESS CYCLE',
                                    x_creation_date            => SYSDATE,
                                    x_created_by               => 0,
                                    x_last_update_date         => SYSDATE,
                                    x_last_updated_by          => 0,
                                    x_last_update_login        => -1
                                   );

   DBMS_OUTPUT.put_line (ln_rowid);
   fnd_lookup_values_pkg.insert_row (x_rowid                    => ln_rowid1,
                                     x_lookup_type              => 'XXND BUSINESS CYCLE',
                                     x_security_group_id        => 0,
                                     x_view_application_id      => 3,
                                     x_lookup_code              => 'XXND_INFO',
                                     x_tag                      => NULL,
                                     x_attribute_category       => NULL,
                                     x_attribute1               => NULL,
                                     x_attribute2               => NULL,
                                     x_attribute3               => NULL,
                                     x_attribute4               => NULL,
                                     x_enabled_flag             => 'Y',
                                     x_start_date_active        => TO_DATE ('01-JAN-1950',
                                                                            'DD-MON-YYYY'
                                                                           ),
                                     x_end_date_active          => NULL,
                                     x_territory_code           => NULL,
                                     x_attribute5               => NULL,
                                     x_attribute6               => NULL,
                                     x_attribute7               => NULL,
                                     x_attribute8               => NULL,
                                     x_attribute9               => NULL,
                                     x_attribute10              => NULL,
                                     x_attribute11              => NULL,
                                     x_attribute12              => NULL,
                                     x_attribute13              => NULL,
                                     x_attribute14              => NULL,
                                     x_attribute15              => NULL,
                                     x_meaning                  => 'XXND Information',
                                     x_description              => NULL,
                                     x_creation_date            => SYSDATE,
                                     x_created_by               => 0,
                                     x_last_update_date         => SYSDATE,
                                     x_last_updated_by          => 0,
                                     x_last_update_login        => -1
                                    );
   DBMS_OUTPUT.put_line (ln_rowid1);
   COMMIT;
END;

Query for Specific User's Responsibilities and Application

2013-12-14T00:23:00.002+05:30

How to find out User Name associate with the Responsibilities and Application........

SELECT fu.user_name                "User Name",
       frt.responsibility_name     "Responsibility Name",
       furg.start_date             "Start Date",
       furg.end_date               "End Date",
       fr.responsibility_key       "Responsibility Key",
       fa.application_short_name   "Application Short Name",
       fat.application_name
FROM fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
WHERE furg.user_id             = fu.user_id
   AND furg.responsibility_id   = frt.responsibility_id
   AND fr.responsibility_id     = frt.responsibility_id
   AND fa.application_id        = fat.application_id
   AND fr.application_id        = fat.application_id
   AND frt.language             = USERENV('LANG')
   AND UPPER(fu.user_name)      = UPPER('NPATIL') --
   AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
ORDER BY frt.responsibility_name;

GL Period Name Query:

2013-12-14T00:21:00.003+05:30

How to find out Open / Close GL Period in ORacle Apps::

SELECT
gps.period_num,
gps.period_name,
gps.set_of_books_id,
gsob.period_set_name,
DECODE (gps.closing_status,
    'O', 'Open',
    'C', 'Closed',
    'F', 'Future',
    'N', 'Never') status
FROM
gl_period_statuses gps,
gl_sets_of_books gsob
where
gps.application_id = 101
and gps.closing_status='O' and
gsob.set_of_books_id = gps.set_of_books_id
and gps.set_of_books_id=fnd_profile.value('GL_SET_OF_BKS_ID')
and gps.PERIOD_YEAR=2013
order by
gps.period_num

Meaning of _ALL _TL in Oracle Apps

2013-12-14T00:21:00.000+05:30

Meaning of _ALL _TL in Oracle Apps

Table-types and names in Oracle Apps:

_ALL are multi-org tables.

_TL are tables corresponding to another table with the same name minus the TL. These tables provide multiple language support. For each item in the table without TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column.

_F these are date tracked tables which occur in HR and Payroll. For these there are two date columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together with the PK identifies a row uniquely. The date intervals cannot overlap.

_B these are the BASE tables.
They are very important and the data is stored in the these table with all validations.
It is supposed that these table will always contain the perfect format data.
If anything happens to the BASE table data, then it is a data corruption issue.

_V are views.

_VL are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV('LANG').

_S are sequences, used for finding new values for the primary key of a table.

_A are Audit Shadow Tables.

_AVn and _ACn are Audit Shadow Views (when data was changed, and with what values).