, , ,

Manual Pricing – OM Sales Order line List price Override

Price list and Modifier to be restricted against the OU level
QP: Security Control                                                      —         OFF to ON

Impacting Profile Options

 

1.Discounting Privilege
It should be set to full or unlimited.
   The button Add Adjustment is displayed based on the combination of the profile “OM: Discounting Privileges” and the “enforce List price” flag for the Order Type. If the order type is not entered then we do not consider the “Enforce List Price” flag and assume it to be false. We look at the profile OM: Discounting Privilege to see if the user has the privilege to apply the manual adjustments. Also if this privilege is FULL, then if the Order Type has Enforce List Price, a manual adjustment cannot be applied. If the privilege is None the user can never apply a manual adjustment. If the profile is

Ref: Master Note: Common Reasons for Error APP-ONT-250274 – No Manual Discount Available, Online Discounting Is Not Allowed; Order Type Enforce List Price [ID 1105868.1]


OM: List Price Override Privilege                         —         Unlimited Access
SO Price Override
ASO : Discounting Privilege                                —         None to Full       (Site)
OE: Discounting Privilege                                   —         Null to Unlimited            (User)
OM: Discounting Privileges                                 —         Null to Unlimited            (User)
QP: Blind Discount Option                                  —         No to Yes          (Site)
OM: Preinsert Manual Adjustments                     —         No to Yes          (Site)
1. Create Modifier (Discount) and Uncheck Automatic flag to both header and line,
2. Line level Enable Override check box then Header qualifier to add the price list  à Save
Now try to create the sales Order à go the lines enter the Item & Qty save or Book SO after change unit selling price now allow the manual override.

/by
, , , , , ,

Oracle EBS Valuation and Other Accounts

Valuation Accounts

You choose a default valuation account when you define organization parameters. Under standard costing, these accounts are defaulted when you define subinventories and can be overridden. Under average costing, these accounts (except for Expense) are used for subinventory transactions and cannot be updated.

Material An asset account that tracks material cost. For average costing, this account holds your inventory and intransit values. Once you perform transactions, you cannot change this account.
Material Overhead An asset account that tracks material overhead cost.
Resource An asset account that tracks resource cost.
Overhead An asset account that tracks resource and outside processing overheads.
Outside processing An asset account that tracks outside processing cost.
Expense The expense account used when tracking a non-asset item.

Other Accounts

Sales The profit and loss (income statement) account that tracks the default revenue account.
Cost of Goods Sold The profit and loss (income statement) account that tracks the default cost of goods sold account.
Purchase Price Variance The variance account used to record differences between purchase order price and standard cost. This account is not used with the average cost method.
Inventory A/P Accrual The liability account that represents all inventory purchase order receipts not matched in Accounts Payable, such as the uninvoiced receipts account.
Invoice Price Variance The variance account used to record differences between purchase order price and invoice price. This account is used by Accounts Payable to record invoice price variance.
Encumbrance An expense account used to recognize the reservation of funds when a purchase order is approved.
Average Cost Variance
Under average costing with negative quantity balances, this account represents the inventory valuation error caused by issuing your inventory before your receipts.




Note: For standard costing, only the Purchase Price Variance, Inventory A/P Accrual, Invoice Price Variance, Expense, Sales and Cost of Goods Sold accounts are required. The other accounts are used as defaults to speed your set up.
Note: For average costing, only the Material, Average Cost Variance, Inventory A/P Accrual, Invoice Price Variance, Expense, Sales and Cost of Goods Sold accounts are required. The other accounts are used as defaults or are not required.

Inter-Organization Transfer Accounts

You define default inter-organization transfer accounts in the Organization Parameters window. These accounts are defaulted when you set up shipping information in the Inter-Organization Shipping Networks window.
Transfer Credit
The default general ledger account used to collect transfer charges when this organization is the shipping organization. This is usually an expense account.
Purchase Price Variance
The default general ledger account used to collect the purchase price variance for inter-organization receipts into standard cost organizations. This is usually an expense account.
Payable
The default general ledger account used as an inter-organization clearing account when this organization is the receiving organization. This is usually a liability account.
Receivable
The default general ledger account used as an inter-organization clearing account when this organization is the shipping organization. This is usually an asset account.
Intransit Inventory
The default general ledger account used to hold intransit inventory value. This is usually an asset account. For average cost organizations, this account is the default material account.

Defining Other Account Parameters

To define Receiving Account information:
1. Navigate to the Organization Parameters window.
2. Select the Other Accounts alternative region.
3. Enter a general ledger account to accumulate Purchase Price Variance for this organization.
This is the variance that you record at the time you receive an item in inventory, and is the difference between the purchase order cost and an item’s standard cost. Purchase price variance is calculated as:
PPV = (PO unit price – standard unit cost) X quantity received
Purchase price variance is not used for average costing.
4. Enter a general ledger account to accumulate Invoice Price Variance for this organization. This is usually an expense account.
Invoice price variance is the difference between the purchase order price for an inventory item and the actual invoice price multiplied by the quantity invoiced. Oracle Inventory passes this account to Oracle Purchasing when the requisition or purchase order is created. When Oracle Payables matches and approves the invoice, Oracle Payables uses the invoice price variance account from the purchase order to record invoice price variance entries. In addition, if you have exchange rate variances, Oracle Payables also records invoice price variance for exchange rate gains and losses.
5. Enter a general ledger account to accumulate Inventory Accounts Payable Accrual for this organization.
This is the account used by Oracle Purchasing to accrue your payable liabilities when you receive your items. This account represents your uninvoiced receipts and is usually part of your Accounts Payable Liabilities in the balance sheet. Oracle Payables relieves this account when the invoice is matched and approved.
6. Enter a default general ledger account to accumulate Encumbrance for this organization. This is the default account when you define your subinventories.
To define Profit and Loss Account information:
1. Select the Other Accounts alternative region.
2. Enter a default Sales revenue account.
When you define your items, this account is defaulted to the item’s sales account in the Invoicing attribute group.
3. Enter a default Cost of Goods Sold account.
When you define your items, this account is defaulted to the item’s cost of goods sold account in the Costing attribute group.
To define Average Cost Account information:
1. Select the Other Accounts alternative region.
2. Under average costing with negative quantity balances, this account represents the inventory valuation error caused by issuing your inventory before processing your receipts. This account is required only when using average costing.
3. Save your work.

Subinventory General Ledger Account Fields

Material
Enter a general ledger account to accumulate material costs for items received into this subinventory. This is usually an asset account used for the value of goods stored in this subinventory.
For asset items, you use this account as a default when you generate purchase requisitions from MRP, min-max organization level planning, or reorder point planning. However, when you receive the purchase order, you use the appropriate valuation or expense account.
Outside Processing
Enter a general ledger account to accumulate outside processing costs for this subinventory. This is usually an asset account. Oracle Work in Process charges this account at standard cost when you receive items for a job or schedule in Oracle Purchasing. Oracle Work in Process relieves this account at standard cost when you issue components to a job or schedule.
Material Overhead
Enter a general ledger account to accumulate material overhead or burden costs for this subinventory. This is usually an asset account.
Overhead
Enter a general ledger account to accumulate resource or department overhead costs for this subinventory. This is usually an asset account. Oracle Work in Process charges this account at standard cost when you complete assemblies from a job or schedule. Oracle Work in Process relieves this account at standard when you issue components to a job or schedule.
Resource
Enter a general ledger account to accumulate resource costs for this subinventory. This is usually an asset account. Oracle Work in Process charges this account at standard cost when you complete assemblies from a job or schedule. Oracle Work in Process relieves this account at standard cost when you issue components to a job or schedule.
Expense
Enter a general ledger account to accumulate expenses for this subinventory. For expense subinventories, this account is charged when you receive any item. For asset subinventories, this account is charged when you receive an expense item.
Encumbrance
ORACLE PURCHASING ONLY
Enter a general ledger account to hold the value of encumbrances against items in this subinventory. This account is used for purchase order receipts and returns.
/by
, , ,

Fixed Assets Basic Setup Steps

Step 1 [Required]

System Controls
Navigator: Setup->Asset System->System Controls
Purpose: Use this form to set up or review the asset system controls.

Step 2 [Required]

Define Fiscal Years
Navigator: Setup->Asset System-> Fiscal Years
Purpose: Use this form to set up or review fiscal years.

Step 3 [Required]

Define Calendar(s)
Navigator: Setup->Asset System-> Calendars
Purpose: Use this form to set up or review calendars.

Step 4 [Required] – with defaults

Account Generator
Navigator: Setup->Financials->Flexfields->Key->Accounts
Purpose: Use this form to customize the Account Generator. Oracle Assets uses the Account Generator to generate accounting flexfield combinations for journal entries. You must review the default process that Oracle Assets uses to see if it meets your accounting requirements. You can optionally customize the Account Generator for each set of books that you have defined. Oracle Workflow must be set up in order to use the Account Generator.

Step 5 [Required]

Define Prorate Conventions
Navigator: Setup->Asset System->Prorate Conventions
Purpose: Use this form to setup or review Prorate conventions and Retirement conventions.

Step 6 [Required] – with Defaults

Define QuickCodes – Lookups
Navigator: Setup->Asset System-> QuickCodes
Purpose: Use this form to set up or review the list of values as needed. During asset entry and maintenance, use the List of Values to choose the QuickCode values created via this form.

Step 7 [Required]

Define Category Key Flexfield Value Set
Navigator: System Administrator ->Application->Validation->Set
Purpose: Use this form to define your value sets.

Step 8 [Required]

Define Location Key Flexfield Value Set
Navigator: System Administrator ->Application->Validation->Set
Purpose: Use this form to define your value set.

Step 9 [Required]

Define Asset Key Flexfield Value Set
Navigator: System Administrator ->Application->Validation->Set
Purpose: This screen is used to define the Value Sets for each segment used in the Asset key flexfield. There are seven possible segments and Oracle recommends that no more than three segments be used. This is a Global and site specific variable.

Step 10 [Required]

Define Category Key Flexfield Segments
Navigator: Fixed Assets Manager ->Setup->Financials->Flexfields->Key->Segments
Purpose: Use key Flexfields to customize your application to fit unique business needs.

Step 11 [Required]

Define Location Key Flexfield Segments
Navigator: Fixed Assets Manager ->Setup->Financials->Flexfields->Key->Segments
Purpose: Use this form to define the descriptive information, validation information, appearance of the pop-up window, number and order of the segments, and the segments descriptions and default values.

Step 12 [Required]

Define Asset Key Flexfield Segments
Navigator: Fixed Assets Manager ->Setup->Financials->Flexfields->Key->Segments
Purpose: Use key Flexfields to customize your application to fit unique business needs.

Step 13 [Required]

Define Category Key Flexfield Segment Values
Navigator: Fixed Assets Manager ->Setup->Financials->Flexfields->Key->Values
Purpose: Use this form to define valid values for the Category key flexfield segment.

Step 14 [Required]

Define Location Key Flexfield Segment Values
Navigator: Fixed Assets Manager ->Setup->Financials->Flexfields->Key->Values
Purpose: Use this form to define valid values for the Category key flexfield segment.

Step 15 [Required]

Define Asset Key Flexfield Segment Values
Navigator: Fixed Assets Manager ->Setup->Financials->Flexfields->Key->Values
Purpose: Use this form to define valid values for the asset key flexfield segment.

Step 16 [Mandatory]

Establish Security by Book – Create Assets Hierarchy
Navigator: Fixed Assets Manager ->Setup -> Security -> Organization -> Hierarchy
Purpose: Use this form to restrict access to data in a specific book by responsibility.
Restricts access to data in a specific Corporate Fixed Asset Book by responsibility.

Step 17 [Required]

Define Book Controls using Security by Book
Navigator: Fixed Assets Manager ->Setup -> Security -> Organization -> Description
Purpose: Use this form to setup the corporate, tax, and budget depreciation books. books must be set up before assets can be assigned.
You must set up your book via the Organization security form in order to establish Security by Book.

Step 18 [Mandatory]

Establish Security by Book – Add site specific OU to Assets Hierarchy
(For each new Operating Unit this will need to be completed)
Navigator: Fixed Assets Manager ->Setup -> Security -> Organization -> Hierarchy
Purpose: Use this form to restrict access to data in a specific book by responsibility.

Step 19 [Manadatory]

Establish Security by Book – Security Profile
Navigator: Fixed Assets Manager ->Setup -> Security -> Security
Purpose: Use this form to restrict access to data in a specific book by responsibility. Verify that the Business Group is created correctly

Step 20 [Mandatory]

Establish Security by Book – Run Security List Maintenance program
Change Responsibility to Global HR Manager
Navigator: Processes and Reports -> Submit Processes and Reports
Purpose: Use this form to define the LOVs with the appropriate books.
Updates the LOVs with the appropriate books. This program is not a report, so there will not be any output produced. This is a critical step. It completes the organization and security profile setup.

Step 21 [Mandatory]

Assign Profile Option Values
Change Responsibility to System Administrator
Navigator: Profile > System
Purpose: Assign application level and responsibility level profile options.
settings assigned at the responsibility level effectively complete the security by book setup by tying the security profile to the responsibility.

Step 22 [Required]

Define Value Set Security
(For each new Operating Unit, this will need to be completed)
Navigator: Security -> Responsibility -> ValueSet -> Define
Purpose: Define security for all country defined value sets created. Restricts access to allowed values for a particular value set

Step 23 [Required]

Assign Value Set Security Rules to Responsibilities
(For each new Operating Unit, this will need to be completed)
Navigator: Security > Responsibility > ValueSet > Assign
Purpose: Assigns previously defined security rules for each applicable responsibility. Restricts access to allowed values for a particular value sets by assigning the predefined rules to responsibilities

Step 24 [Required]

Define Asset Categories
(Change Responsibility to Fixed Assets Manager)
Navigator: Setup->Asset System-> Asset Categories
Purpose: Use this form to set up or review asset category information. Category information is common for a group of assets. Oracle Assets defaults this information when you add an asset, to help you add assets quickly. If the default does not apply, you can override many of the defaults for an individual asset in the Additions or Depreciation Books forms. You set up default values for each category in each book. The default depreciation rules that you set up for a category also depend upon the date placed in service ranges you specify.

Step 25 [Required]

Define Location Codes
Navigator: Setup->Asset System->Locations
Purpose: Use this form to set up or review the location flexfield combinations that are valid for your site. Location information is used for Responsibility and Property Tax reports.

Step 26 [Required]

Define Asset Keys
Navigator: Setup->Asset System->Locations
Purpose: At least one Asset Key (default) must be setup for each OU. Use this form to set up or review the asset key flexfield combinations that are valid for your site. Asset Key information is used for CAR Reporting.

Step 27 [Optional]

Define Descriptive Flexfields
Navigator: Setup->Financials->Flexfields->Descriptive->Segments
Purpose: Use this form to define your descriptive flexfield.

Step 28 [Required] – with Defaults

Define Depreciation Methods
Navigator: Setup->Depreciation->Methods
Purpose: Use this form to setup or define depreciation methods. . Oracle Assets includes many standard depreciation methods, and you can define additional methods in this window, if necessary. For Corporate purposes, you must use Straight Line method.
The life of a previously set up method cannot be changed; so a new method would have to be entered.

Step 29 [Optional]

Define Depreciation Ceilings
Navigator: Setup->Depreciation->Ceiling
Purpose: Use this form to setup or review depreciation ceilings. Depreciation ceilings limit the annual depreciation expense taken on an asset.

Step 30 [Optional]

Define Investment Tax Credit Rates
Navigator: Setup->Depreciation->ITC Rates
Purpose: Use this form to setup or review ITC Rates and recapture rates.

Step 31 [Optional]

Define Investment Tax Credit Recapture Rates
Navigator: Setup->Depreciation->ITC Recapture Rates
Purpose: Use this form to setup or review ITC recapture rates.

Step 32 [Optional]

Define Price Indexes
Navigator: Setup->Asset System->Price Indexes
Purpose: Use this form to setup or review Price Indexes.

Step 33 [Optional]

Define Leases/Lease Payment Schedules
Navigator: Setup->Asset System->Lease->Lease Details
Purpose: Use the Lease Details window and the Lease Payments window to define new leases.
You cannot update or delete leases that are in use.

Step 34 [Optional]

Define Warranties
Navigator: Setup->Asset System->Warranties
Purpose: Use this form to define Warranties for tracking. Define and track descriptive information on manufacturer and vendor warranties.

Step 35 [Optional]

Define Distribution Sets
Navigator: Setup->Asset System->Distribution Sets
Purpose: Use this form to define default distribution sets. Define default distribution sets. These previously defined distribution sets can be selected via a poplist in the Assignments window to quickly assign the appropriate distributions to new assets.

Step 36 [Optional]

Define Bonus Depreciation Rules
Navigator: Setup->Depreciation->Bonus Rules
Purpose: Use this form to define bonus depreciation rules. Bonus rates let you increase the annual depreciation expense for assets using flat-rate depreciation methods.

/by
, , , , , ,

Form Personalization from Sales Order form and Purchase Order form aganist the Item

Form Personalization for Purchase Order and Sales Order while choose Item from procure material from Purchase order form or sell the Material from Sales Order form at the time we should know the that particular material is
  1. VATABLE
  2. EXCISEABLE
  3. RECOVERABLE (CLAIMABLE)
  4. ITEM CLASS ENABLED
    The above all condition satisfied we can proceed to procure material or sell the material other wise give the note message for warning but it should not stop any transaction.
Refer the screen shots below…
Expected output form Sales Order form
Form personalization
Nav:Open the form and choose Object trigger value → Help → Diagnostics → Custom code → Personalize
Condition :
PELEXVAT(${item.LINE.ORDERED_ITEM_DSP.value},${item.LINE.ship_from_org_id.value})=2
 Message Text :
= ‘THIS ‘ || :LINE.ORDERED_ITEM_DSP || ‘ ITEM EXCISE OR VAT NOT DEFINED PLS CHECK.’
 
PELEXVAT (Procedure)
CREATE OR REPLACE FUNCTION APPS.PELEXVAT(ITEM_CODE VARCHAR2,SHIP_ORG_ID NUMBER) RETURN Number 
IS 
FLAG Number;
BEGIN
Select (Case When Flag=0 Then 1 Else 2 End) as Flag Into Flag From(
Select Abs(Sum(F1+F2)-Sum(F3+F4)) Flag From(
Select (Case When Sum(F1)<>0 Then Sum(F1) Else 3 End ) as F1,
(Case When Sum(F2)<>0 Then Sum(F2) Else 4 End) as F2,
(Case When Sum(F3)<>0 Then Sum(F3) Else 5 End)as F3,
(Case When Sum(F4)<>0 Then Sum(F4) Else 6 End) as F4 From (
Select (Case When F1=1 Then Sum(F1) End) as F1,
(Case When F2=1 Then Sum(F2) End) as F2,
(Case When F3=1 Then Sum(F3) End) as F3,
(Case When F4=1 Then Sum(F4) End) as F4 From (
Select Distinct ( Case When ATTRIBUTE_CODE=’EXCISABLE’ And ATTRIBUTE_VALUE=’Y’ Then 1 Else 5 End) as F1,
( Case When ATTRIBUTE_CODE=’MODVATABLE’ And ATTRIBUTE_VALUE=’Y’ Then 1 Else 4 End) as F2,
( Case When ATTRIBUTE_CODE=’RECOVERABLE’ And ATTRIBUTE_VALUE=’Y’ Then 1 Else 4 End) as F3,
( Case When ATTRIBUTE_CODE=’APPLICABLE’ And ATTRIBUTE_VALUE=’Y’ Then 1 Else 11 End) as F4
From ( Select ATTRIBUTE_CODE,ATTRIBUTE_VALUE, SEGMENT1,CREATION_DATE,TEMPLATE_ID,templ_org_regns_id,
ORGANIZATION_ID,RGM_ITEM_REGNS_ID
From (
select Distinct A.ATTRIBUTE_CODE,ATTRIBUTE_VALUE, SEGMENT1,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’EXCISABLE’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’MODVATABLE’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’APPLICABLE’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’RECOVERABLE’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’ITEM CLASS’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID )))Group By F1,F2,F3,F4)));
DBMS_OUTPUT.Put_Line(Flag);
RETURN FLAG;
END;
/
Expected output form Purchase Order form
 
Form personalization
Nav:Open the form and choose Object trigger value → Help → Diagnostics → Custom code → Personalize
Condition :
PELEXITEMVAT(${item.PO_LINES.ITEM_NUMBER.value},
${item.PO_HEADERS.SHIP_TO_ORG_ID.value})<>5
OR
PELEXITEMVAT(${item.PO_LINES.ITEM_NUMBER.value},
${item.PO_HEADERS.SHIP_TO_ORG_ID.value}) =0
PELEXITEMVAT (Procedure)
CREATE OR REPLACE Function APPS.PELEXITEMVAT(ITEM_CODE VARCHAR2,SHIP_ORG_ID NUMBER) RETURN Number
IS
FLAG Number;
BEGIN
Select (Case When Flag Is Null Then 0 Else Flag End ) as Flag Into Flag From (
Select Sum(F1)+Sum(F2)+SUM(F3)+SUM(F4)+SUM(F5) as Flag From (
Select (Case When F1=1 Then Sum(F1) End) as F1,
(Case When F2=1 Then Sum(F2) End) as F2,
(Case When F3=1 Then Sum(F3) End) as F3,
(Case When F4=1 Then Sum(F4) End) as F4,
(Case When F5=1 Then Sum(F5) End) as F5 From (
Select Distinct ( Case When ATTRIBUTE_CODE=’EXCISABLE’ Then 1 Else 5 End) as F1,
( Case When ATTRIBUTE_CODE=’MODVATABLE’ Then 1 Else 4 End) as F2,
( Case When ATTRIBUTE_CODE=’RECOVERABLE’ Then 1 Else 4 End) as F3,
( Case When ATTRIBUTE_CODE=’APPLICABLE’ Then 1 Else 11 End) as F4,
( Case When ATTRIBUTE_CODE=’ITEM’ Then 1 Else 11 End) as F5
From (
Select ATTRIBUTE_CODE,ATTRIBUTE_VALUE, SEGMENT1,CREATION_DATE,TEMPLATE_ID,
templ_org_regns_id,ORGANIZATION_ID,RGM_ITEM_REGNS_ID
From (
select Distinct A.ATTRIBUTE_CODE,ATTRIBUTE_VALUE, SEGMENT1,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’EXCISABLE’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’MODVATABLE’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’APPLICABLE’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’RECOVERABLE’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’ITEM CLASS’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct ‘ITEM’ as ATTRIBUTE_CODE, ‘ITEM’ as ATTRIBUTE_VALUE, B.SEGMENT1 ,Null as CREATION_DATE,0 as TEMPLATE_ID,0 as templ_org_regns_id,B.ORGANIZATION_ID,0 as RGM_ITEM_REGNS_ID
From jai_rgm_tmpl_itm_regns A,Mtl_System_Items_B B
Where A.INVENTORY_ITEM_ID=B.INVENTORY_ITEM_ID And
B.SEGMENT1 =ITEM_CODE
And B.ORGANIZATION_ID=SHIP_ORG_ID )))Group By F1,F2,F3,F4,F5));
DBMS_OUTPUT.Put_Line(Flag);
RETURN FLAG;
END;
/
/by