Sunday, October 9, 2022

Query to Get Profile value at any level

Query to Get Profile value at any level  

SELECT 

  distinct pot.user_profile_option_name Profile, 

  DECODE (

    a.profile_option_value, '1', '1 (may be "Yes")', 

    '2', '2 (may be "No")', a.profile_option_value

  ) Value, 

  DECODE (

    a.level_id, 10001, 'Site', 10002, 'Application', 

    10003, 'Responsibility', 10004, 'User', 

    '????'

  ) Level_identifier, 

  DECODE (

    a.level_id, 10002, e.application_name, 

    10003, c.responsibility_name, 10004, 

    d.user_name, '-'

  ) Level_Name 

FROM 

  applsys.fnd_application_tl e, 

  applsys.fnd_user d, 

  applsys.fnd_responsibility_tl c, 

  applsys.fnd_profile_option_values a, 

  applsys.fnd_profile_options b, 

  applsys.fnd_profile_options_tl pot 

WHERE 

  1 = 1 

  AND UPPER (pot.user_profile_option_name) LIKE UPPER ('%&v_profile%') 

  AND pot.profile_option_name = b.profile_option_name 

  AND pot.language = userenv('lang') 

  AND b.application_id = a.application_id(+) 

  AND b.profile_option_id = a.profile_option_id(+) 

  AND a.level_value = c.responsibility_id(+) 

  AND a.level_value = d.user_id(+) 

  AND a.level_value = e.application_id(+) 

  AND e.language(+) = userenv('lang') 

  AND (

    UPPER (e.application_name) LIKE UPPER ('%&appname_respname_username%') 

    OR UPPER (c.responsibility_name) LIKE UPPER (

      '%&&appname_respname_username%'

    ) 

    OR UPPER (d.user_name) LIKE UPPER (

      '%&&appname_respname_username%'

    )

  ) 

ORDER BY 

  Profile, 

  Level_identifier, 

  Level_name, 

  Value


Bom Explosion Query

Below query can be handy to explode and Query BOM Details

SELECT 

  DISTINCT LPAD (' ', LEVEL * 2) || LEVEL order_level, 

  msib.segment1 assembly_item, 

  msib.description assembly_description, 

  msib.inventory_item_status_code assembly_item_status, 

  SYS_CONNECT_BY_PATH (msib2.segment1, '/') PATH, 

  msib2.segment1 AS component_item, 

  msib2.description component_item_description, 

  msib2.inventory_item_status_code component_item_status, 

  bic.item_num, 

  bic.operation_seq_num, 

  bic.component_quantity 

FROM 

  bom.bom_components_b bic, 

  bom.bom_structures_b bom, 

  inv.mtl_system_items_b msib, 

  inv.mtl_system_items_b msib2, 

  mtl_parameters mp 

WHERE 

  1 = 1 

  AND bic.bill_sequence_id = bom.bill_sequence_id 

  AND SYSDATE BETWEEN bic.effectivity_date 

  AND Nvl(bic.disable_date, SYSDATE) 

  AND bom.assembly_item_id = msib.inventory_item_id 

  AND bom.organization_id = msib.organization_id 

  AND bic.component_item_id = msib2.inventory_item_id 

  AND bom.organization_id = msib2.organization_id 

  AND mp.organization_id = msib.organization_id 

  AND mp.organization_code = : p_org_code 

  /* organization here */

  AND bom.alternate_bom_designator IS NULL START WITH msib.segment1 = : p_item_number 

  /*  component item to be used here */

  CONNECT BY NOCYCLE PRIOR bic.component_item_id = msib.inventory_item_id 

ORDER BY 

  PATH 

Oracle ERP - Hierarchical BOM Query

The query below shows the use of hierarchical query syntax to list the parts of an assembly in a typical bill of material (BOM). The parts are setup in a hierarchical (assembly - component/part) or indented manner.

 


SELECT 

  CONNECT_BY_ROOT q_bom.assembly_num root_assembly, 

  q_bom.assembly_num, 

  q_bom.component_num, 

  q_bom.qty, 

  SUBSTR(

    SYS_CONNECT_BY_PATH(q_bom.assembly_Num, ' <-- '), 

    5

  ) assembly_path, 

  LEVEL bom_level, 

  CONNECT_BY_ISCYCLE is_cycle 

FROM 

  (

    SELECT 

      mb1.segment1 assembly_num, 

      mb2.segment1 component_num, 

      bc.component_quantity qty 

    FROM 

      bom.bom_components_b bc, 

      bom.bom_structures_b bs, 

      inv.mtl_system_items_b mb1, 

      inv.mtl_system_items_b mb2 

    WHERE 

      bs.assembly_item_id = mb1.inventory_item_id 

      AND bc.component_item_id = mb2.inventory_item_id 

      AND bc.bill_sequence_id = bs.bill_sequence_id 

      AND mb1.organization_id = mb2.organization_id 

      AND bs.organization_id = mb2.organization_id 

      AND bc.disable_date Is Null 

      AND bs.alternate_bom_designator IS NULL 

      AND mb1.organization_id = 90101110109

  ) q_bom START WITH q_bom.assembly_Num = '&i_comp' CONNECT BY NOCYCLE PRIOR q_bom.component_num = q_bom.assembly_num ORDER SIBLINGS BY q_bom.assembly_Num;


Converting rows into columns using pivot function


we can convert rows into columns using the pivot function

let us consider the following data as sample data


DEPTNO ENAME JOB

10 ALLEN ANALYST

10 JONES CLERK

10 FORD MANAGER

10 ABCD PRESIDENT

10 CLARK SALESMAN

20 MILLER ANALYST

20 SMITH CLERK

20 WARD MANAGER

20 efgh PRESIDENT

20 MARTIN SALESMAN

30 SCOTT ANALYST

30 TURNER CLERK

30 ADAMS MANAGER

30 BLAKE PRESIDENT

30 KING SALESMAN


now create a table as emp_jobs with this data



using pivot function

we are converting rows into columns

select * from emp_jobs pivot( min(ename) for job in ('PRESIDENT','MANAGER','CLERK','ANALYST','SALESMAN'));



the output is in the form

DEPTNO PRESIDENT' MANAGER' CLERK' ANALYST' SALESMAN'

20 efgh WARD SMITH MILLER MARTIN

10 ABCD FORD JONES ALLEN CLARK

30 BLAKE ADAMS TURNER SCOTT KING

but while using pivot function aggregate function should be used 


we can also achieve this conversion without using pivot function using this sql query

 select

   a1.deptno,

   a1.ename  AS PRESIDENT,

   a2.ename AS MANAGER,

   a3.ename AS CLERK

from

   emp_jobs  a1,

   emp_jobs a2,

   emp_jobs  a3

   where

   a1.deptno=a2.deptno

   and a2.deptno=a3.deptno

   and a3.deptno=a1.deptno

   and a1.job = 'PRESIDENT'

   and a2.job = 'MANAGER'

   and a3.job ='CLERK';

Query to get Purchase Order and requisition in Oracle Fusion

SELECT 

  POH.PO_HEADER_ID, 

  POH.SEGMENT1, 

  PRHA.REQUISITION_NUMBER 

FROM 

  PO_HEADERS_ALL POH, 

  PO_DISTRIBUTIONS_ALL PDA, 

  POR_REQ_DISTRIBUTIONS_ALL PRDA, 

  POR_REQUISITION_LINES_ALL PRLA, 

  POR_REQUISITION_HEADERS_ALL PRHA 

WHERE 

  1 = 1 

  AND POH.PO_HEADER_ID = PDA.PO_HEADER_ID 

  AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID 

  AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID 

  AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID 

  AND PRHA.REQUISITION_NUMBER = < Requsition_number >

 

Query to get AP Invoice lines Tax details in Oracle Fusion

 Query to get AP Invoice lines Tax details in Oracle Fusion 

SELECT 

  aia.invoice_num, 

  xep.name Legal_Entity_Name, 

  psv.vendor_name party, 

  aila.line_number Line_Ref, 

  aila.line_type_lookup_code, 

  gcc.segment1 Distribution_entity, 

  zl.tax_amt Tax_amount, 

  zl.tax_regime_code Tax_regime, 

  zl.tax_jurisdiction_code Tax_Juridiction, 

  zl.taxable_amt TaxableAmt, 

  ZE.exception_reason_code Exemption_Reason, 

  ZE.tax_rate_code Exemption_Rate, 

  zrb.country_code Country_of_taxation, 

  zl.tax_line_number Tax_Line_Ref, 

  zl.tax, 

  zl.tax_rate_code, 

  zl.tax_rate 

FROM 

  ap_invoices_all aia, 

  poz_suppliers_v psv, 

  xle_entity_profiles xep, 

  ap_invoice_lines_all aila, 

  gl_code_combinations gcc, 

  ap_invoice_distributions_all aida, 

  zx_lines zl, 

  zx_status_b zsb, 

  zx_exceptions ze, 

  zx_regimes_b zrb 

WHERE 

  1 = 1 

  AND psv.vendor_id = aia.vendor_id 

  AND aia.legal_entity_id = xep.legal_entity_id(+) 

  AND aia.invoice_id = aila.invoice_id 

  AND aila.invoice_id = aida.invoice_id 

  AND aila.line_number = aida.invoice_line_number 

  AND aia.invoice_id = zl.trx_id(+) 

  AND aila.line_number = zl.trx_line_number(+) 

  AND zl.application_id(+) = 200 

  AND ze.tax_exception_id(+) = zl.tax_exception_id 

  AND zsb.tax_status_id = zl.tax_status_id 

  AND zl.tax_regime_id = zrb.tax_regime_id 

  AND aida.dist_code_combination_id = gcc.code_combination_id 

  AND (

    aida.line_type_lookup_code = 'MISCELLANEOUS' 

    OR aida.line_type_lookup_code = 'FREIGHT' 

    OR aida.line_type_lookup_code = 'ITEM'

  ) 

  AND zsb.tax_status_code = 'EXEMPT'

Query to fetch Level -1 BOM Details for an Inventory Item HOME » QUERY TO FETCH LEVEL -1 BOM DETAILS FOR AN INVENTORY ITEM

 Query to fetch Level -1 BOM Details for an Inventory Item HOME » QUERY TO FETCH LEVEL -1 BOM DETAILS FOR AN INVENTORY ITEM 



SELECT 

  msi.segment1 “Parent material coding”, 

  msi.inventory_item_id, 

  msi.description “Parent item description”, 

  msi1.segment1 “Sub item code”, 

  msi1.inventory_item_id sub_item_id, 

  msi1.description “Sub item description” 

FROM 

  mtl_system_items_b msi, 

  bom_bill_of_materials bom, 

  bom_inventory_components bic, 

  mtl_system_items_b msi1 

WHERE 

  1 = 1 

  AND msi.organization_id = : organization_id 

  AND msi.inventory_item_id = bom.assembly_item_id 

  AND msi.organization_id = bom.organization_id 

  AND bom.organization_id = : organization_id 

  AND bom.alternate_bom_designator IS NULL 

  AND bom.bill_sequence_id = bic.bill_sequence_id 

  AND bic.disable_date IS NULL 

  AND bic.component_item_id = msi1.inventory_item_id 

  AND msi1.organization_id = : organization_id 

  AND msi.segment1 =: item_number;

Friday, October 7, 2022

OM Related queries Oracle Apps BOM Tables and Queries

 OM Related queries ============== Oracle Apps BOM Tables and Queries


BOM_DEPARTMENTS Departments 

BOM_DEPARTMENT_CLASSES Department classes 

BOM_DEPARTMENT_RESOURCES Resources associated with departments

BOM_OPERATIONAL_ROUTINGS Routings 

BOM_OPERATION_NETWORKS Routing operation networks 

BOM_OPERATION_RESOURCES Resources on operations 

BOM_OPERATION_SEQUENCES Routing operations

 BOM_OPERATION_SKILLS 

BOM_RESOURCES Resources, overheads, material cost codes, and material overheads 

BOM_STANDARD_OPERATIONS Standard operations 

BOM_ALTERNATE_DESIGNATORS Alternate designators 

BOM_COMPONENTS_B Bill of material components 

BOM_STRUCTURES_B Bills of material 

BOM_STRUCTURE_TYPES_B Structure Type master table 

BOM_BILL_OF_MATERIALS 

BOM_PARAMETERS

 BOM_DEPENDENT_DESC_ELEMENTS 

BOM_REFERENCE_DESIGNATORS 

BOM_SUBSTITUTE_COMPONENTS 

FND_DOCUMENTS 

FND_DOCUMENTS_TL 

FND_DOCUMENTS_SHORT_TEXT 

FND_ATTACHED_DOCUMENTS 


Select 

  * 

from 

  MFG_LOOKUPS 

WHERE 

  --LOOKUP_CODE =  'BOM_ITEM_TYPE'

  --LOOKUP_CODE =  'WIP_SUPPLY_TYPE'

  --LOOKUP_CODE =  'BOM_ASSEMBLY_TYPE'

  -----------------------------------------------------------------

1) Query for BOM Details 

SELECT 

  d.inventory_item_id, 

  d.concatenated_segments "Assembly_item", 

  e.organization_code "Organization", 

  nvl(

    a.ALTERNATE_BOM_DESIGNATOR, '10'

  ) "ALTERNATIVE BOM", 

  b.ITEM_NUM "ITEM", 

  c.concatenated_segments "COMPONENT", 

  b.component_quantity "UNIT", 

  c.primary_uom_code "QUANTITY", 

  d.primary_uom_code "UNIT" 

FROM 

  bom_bill_of_materials a, 

  bom_inventory_components b, 

  mtl_system_items_kfv c, 

  mtl_system_items_kfv d, 

  org_organization_definitions e 

WHERE 

  1 = 1 

  AND a.organization_id in (: P_organization_id) 

  AND (

    a.BILL_SEQUENCE_ID = b.BILL_SEQUENCE_ID 

    OR a.COMMON_BILL_SEQUENCE_ID = b.BILL_SEQUENCE_ID

  ) 

  AND b.implementation_date IS NOT NULL 

  AND b.DISABLE_DATE IS NULL 

  AND a.organization_id = c.organization_id 

  AND b.COMPONENT_ITEM_ID = c.inventory_ITEM_ID 

  AND a.assembly_item_id = d.inventory_item_id 

  AND a.organization_id = d.organization_id 

  AND a.organization_id = e.organization_id --AND    c.organization_id   = :P_organization_id

  --AND  a.ASSEMBLY_ITEM_ID  = :P_assembly_item_id

  -- AND  ALTERNATE_BOM_DESIGNATOR IS NULL

ORDER BY 

  e.organization_code, 

  d.inventory_item_id, 

  ALTERNATE_BOM_DESIGNATOR, 

  b.ITEM_NUM;

----------------------------------------------------------------

2) Query for Resources 

SELECT 

  c.concatenated_segments "Assemly_Item", 

  d.organization_code "Organization", 

  a.alternate_routing_designator "Alternate_routing", 

  b.operation_seq_num "OPERATION NUMBER", 

  e.department_code "WORK CENTER", 

  b.operation_description "DESCRIPTION(Operation)", 

  c.primary_uom_code "OPERATION UNIT", 

  G.uom "UNIT", 

  f.operation_code, 

  g.resource_code, 

  g.USAGE_RATE_OR_AMOUNT h.DESCRIPTION 

FROM 

  bom_operational_routings a, 

  bom_operation_sequences b, 

  mtl_system_items_kfv c, 

  org_organization_definitions d, 

  bom_departments e, 

  bom_standard_operations f, 

  bom_operation_resources_v g, 

  bom_resources h 

WHERE 

  a.organization_id in (: P_organization_id) 

  AND a.organization_id = d.organization_id 

  AND a.organization_id = c.organization_id 

  AND a.assembly_item_id = c.inventory_item_id 

  AND a.routing_sequence_id = b.routing_sequence_id 

  AND b.department_id = e.department_id 

  AND b.disable_date is null --AND   a.assembly_item_id = :P_assembly_item_id

  AND f.standard_operation_id(+) = b.standard_operation_id 

  AND g.operation_sequence_id = b.operation_sequence_id 

  AND h.resource_id = g.resource_id 

order by 

  d.organization_code, 

  c.concatenated_segments, 

  a.alternate_routing_designator, 

  b.operation_seq_num --------------------------------------------------------------------------------------------

  

3) Departments and Resources and Departments associated with Resources 

select 

  b.organization_code "Plant", 

  a.DEPARTMENT_CODE "Department", 

  a.DESCRIPTION "Department Description", 

  a.DEPARTMENT_CLASS_CODE "Department Class", 

  c.DESCRIPTION "Department Class Description", 

  d.location_code "Department_location" 

from 

  BOM_DEPARTMENTS a, 

  org_organization_definitions b, 

  BOM_DEPARTMENT_CLASSES c, 

  hr_locations d 

where 

  a.organization_id in (: P_organization_id) 

  AND a.organization_id = b.organization_id 

  AND a.DEPARTMENT_CLASS_CODE = c.DEPARTMENT_CLASS_CODE 

  AND a.organization_id = c.organization_id 

  AND a.location_id = d.location_id(+) 

select 

  b.organization_code "Plant", 

  a.RESOURCE_CODE "Resource Name", 

  a.DESCRIPTION "Resource Description", 

  a.UNIT_OF_MEASURE 

from 

  BOM_RESOURCES a, 

  org_organization_definitions b --,mfg_lookups c

where 

  a.organization_id in (: P_organization_id) 

  AND a.organization_id = b.organization_id 

  AND a.Disable_date is Null --AND    c.lookup_type = 'BOM_BASIS_TYPE'

  --AND    c.LOOKUP_CODE = a.DEFAULT_BASIS_TYPE

  exec mo_global.set_policy_context('S',: P_org_id);

--BOM_RESOURCE_TYPE

--BOM_BASIS_TYPE

select 

  * 

from 

  mfg_lookups 

where 

  lookup_type like 'BOM_BASIS_TYPE' 

select 

  * 

from 

  BOM_DEPARTMENT_RESOURCES 

select 

  * 

from 

  BOM_RESOURCE_SHIFTS 

select 

  b.organization_code "Plant", 

  a.DEPARTMENT_CODE "Department", 

  a.DESCRIPTION "Department Description", 

  a.DEPARTMENT_CLASS_CODE "Department Class", 

  c.DESCRIPTION "Department Class Description", 

  d.location_code "Department_location", 

  f.RESOURCE_CODE "Resource Name", 

  f.DESCRIPTION "Resource Description", 

  f.UNIT_OF_MEASURE 

from 

  BOM_DEPARTMENTS a, 

  org_organization_definitions b, 

  BOM_DEPARTMENT_CLASSES c, 

  hr_locations d, 

  BOM_DEPARTMENT_RESOURCES e, 

  BOM_RESOURCES f -- ,BOM_RESOURCE_SHIFTS g

where 

  a.organization_id in (: P_oranization_id) 

  AND a.organization_id = b.organization_id 

  AND a.DEPARTMENT_CLASS_CODE = c.DEPARTMENT_CLASS_CODE 

  AND a.organization_id = c.organization_id 

  AND a.location_id = d.location_id(+) 

  AND a.DEPARTMENT_ID = e.DEPARTMENT_ID 

  AND e.RESOURCE_ID = f.RESOURCE_ID --AND    a.DEPARTMENT_ID = g.DEPARTMENT_ID

  --AND    f.RESOURCE_ID = g.RESOURCE_ID

  --------------------------------------------------------------------------------------------------------

  Routing Open Interface Concurrent: Bill 

  and Routing Interface -- Create Routing

  insert into bom_op_routings_interface (

    assembly_item_id, process_revision, 

    process_flag, transaction_type, 

    routing_type, organization_id

  ) 

values 

  (

    218967, 

    Null, 

    1, 

    --1 (Pending), 3 (Assign/Validation Failed), 4 (Import Failed) , 7 (Import Succeeded).

    'CREATE', 

    1, 

    ----1) manufacturing, 2) engineering

    204

  );

-- Create Routing Sequences

insert into bom_op_sequences_interface (

  assembly_item_id, operation_seq_num, 

  department_id, department_code, 

  process_flag, transaction_type, 

  organization_id, effectivity_date

values 

  (

    218967, 10, 42877, 'PD1', 1, 'CREATE', 

    204, sysdate

  );

-- Create Operation Resources

insert into bom_op_resources_interface (

  resource_seq_num, 

  resource_id, 

  resource_code, 

  usage_rate_or_amount, 

  --basis_type ,

  assigned_units, 

  --schedule_flag,autocharge_type,

  assembly_item_id, 

  operation_seq_num, 

  --operation_sequence_id,

  process_flag, 

  transaction_type, 

  effectivity_date, 

  organization_id, 

  schedule_flag, 

  schedule_seq_num

values 

  (

    10, 

    90529, 

    'PR1', 

    1, 

    1, 

    218967, 

    10, 

    --2346216 ,

    1, 

    'CREATE', 

    sysdate, 

    204, 

    1, 

    10

  );

select 

  * 

from 

  mtl_system_items_kfv 

where 

  inventory_item_id = 133 

  and organization_id = 204 

select 

  * 

from 

  bom_departments 

where 

  department_id = 42877 

  and organization_id = 204 

select 

  * 

from 

  BOM_DEPARTMENT_RESOURCES 

where 

  department_id = 42877 

select 

  * 

from 

  bom_resources 

where 

  resource_id = 90529 

select 

  * 

from 

  bom_op_resources_interface


Wednesday, February 9, 2022

PL/SQL Developer: Can't Connect to Database Error

 


Right after you install PL/SQL Developer, you realize that you cannot connect to your databases. You get the following message:

ORA-12154: TNS:could not resolve the connect identifier specified











For a moment, you may think, it is something to do with TNSNAMES.ora file or Pinging issue. It is, however, purely a PL/SQL Developer issue. When I downloaded SQL Developer from Oracle and installed it, I could connect to all my databases fine.

The solution is actually pretty simple. There are probably multiple ORACLE_HOME defined (such as for reports and forms, workflow, discoverer, etc.) in your machine, and PL/SQL Developer is probably not finding the correct one to choose from. All you have to do is to choose the correct version for your PL/SQL Developer. To do so, follow the below instruction.
  1. Make sure you are connected to Network/VPN.
  2. Open PL/SQL Developer.
  3. Click on Tools -> Preferences. Select the very first option: Connection.
  4. From Oracle Home drop down list, select the correct version. For example, OH159997875 works for my machine.
  5. Restart PL/SQL Developer.
  6. Cross your fingers and retry to connect to your databases.
  7. If it works, say "Buiya". If it doesn't, scream "F***", and Google search.


Printer type and its significance and How you relate Printer driver and Printer type.

 A printer type identifies a printer by manufacturer and model. A print style tells the printer how a printed output should look. A printer driver delivers commands that tell the printer how to output the specified print style. The ability to print a report in a particular print style depends on the type of printer the report file is sent to.


Step 1. Printer Type: What kind of printer do you have. This is the manufacturer and model. Two examples are a DEC LN03 printer and an HP Laserjet III printer.

Step 2. Print Style: A description of how your report should be printed. Print style determines the:
- Number of lines per page.
- Width of each line.
- Whether a header page should be printed.
- Number of lines per page.
- Width of each line.
- Whether a header page should be printed.

Step 3.Printer Driver: The set of commands that tell a printer how to print in the Print Style chosen.
- Initialization sets printing orientation.
- Reset clears printer’s instructions for next print job.

SetUp Steps
Step 4 - Setup Printer Driver - run on Client
Go into applications as a user with 'System Administrator' responsibility.
Go to Install-->Printer-->Driver
Create a new driver by entering the following information:-
Driver Name : testDriver
User Driver : testDriver
SRW Driver: L
Arguments : print /d:\\\ $PROFILES$.FILENAME (where is the host name returned by the hostname command and is the Share Name you gave to the printer in step (1) above) e.g. 'print /d:\\ukp19999-4w\testprn $PROFILES$.FILENAME'

All other options should be left at default - which is blank for everything except 'Driver Method' which is defaulted to 'Command'.

Step 5 - Setup Print Style - run on Client
Go to Install-->Printer-->Style
Create new style by entering the following information:-
Style Name : testStyle
Seq : 4User
Style : testStyle
SRW Driver : L
Columns : 132
Rows : 64
All other options should be left blank. NOTE - the 'Seq' number needs to be unique, so if you get an error to the effect number 4 has already been used, then use a different number that has not previously been used. The seeded data uses multiples of 5 (ie - 5, 10, 15, etc) up to a value of 185.

Step 6 - Setup Printer Types - run on ClientGo to Install-->Printer-->TypesCreate new type by entering the following information:-
Type : testType
Style : testStyle
Driver Name : testDriver

Step 7 - Register Printer - run on ClientGo to Install-->Printer-->RegisterCreate new printer by entering the following information:-
Printer : testPrinter
Type : testType

Step 8 - Setup profile option - run on ClientGo to Profile-->System Type in 'printer' in the 'Profile' and click on 'Find' This should display the System Profile option called Printer on screen. Click on "Site" and then use the List Of Values to select your printer called'testPrinter' Save this setting, then exit Oracle Applications

Step 9 - Stop and restart Concurrent Manager - run on Server Go to Settings-->Control Panel-->Service Scroll down to and click on 'OracleConcMgr' where is your APPL_CONFIG setting. Click on 'Stop' to stop the service. This could take a couple of minutes whilst the FNDLIBR, etc processes are gracefully stopped. When you are returned to the Services list, click on 'Start' to restart the Concurrent Manager.

FND_GLOBAL.APPS_INITIALIZE in oracle applications

 Setting the Applications Context FND_GLOBAL.APPS_INITIALIZE in oracle applications:


Use the API FND_GLOBAL.APPS_INITIALIZE to set the context of the application in standalone sessions that were not initialized through normal means. Typically, you would use this API in external custom programs that are establishing their own connections.

procedure APPS_INITIALIZE(user_id IN number,resp_id IN number,resp_appl_id IN number security_group_id IN number);
USER_ID - The User ID number.
RESP_ID - The ID number of the responsibility.
RESP_APPL_ID - The ID number of the application too which the responsibility belongs.
SECURITY_GROUP_ID - The ID number of the security GROUP. This argument IS automatically defaulted BY the API. The caller should NOT pass a value FOR it.
Usage: fnd_global.APPS_INITIALIZE (1010,20417,201):
This procedure sets up global variables and profile values in a database session. Call this procedure to initialize the global security context for a database session. You can use it for routines such as PL/SQL or other programs that are not integrated with either the Oracle Applications concurrent processing facility or Oracle Forms (both of which already do a similar initialization for a database session). The typical use for this routine would be as part of the logic for launching a separate non–Forms session from an established Oracle Applications form session. You can also use this procedure to set up a database session for manually testing application code using SQL*Plus. This routine should only be used when the session must be established outside of a normal form or concurrent program connection. You can obtain valid values to use with this procedure by using profile option routines to retrieve these values in an existing Oracle Applications form session.

begin
fnd_global.APPS_INITIALIZE(200131258,20004,51710);
end;

SELECT fnd_profile.value (‘RESP_ID’) FROM dual
SELECT fnd_profile.value (‘USER_ID’) FROM dual
SELECT fnd_profile.value (‘APPLICATION_ID’) FROM dual
SELECT TO_NUMBER (FND_PROFILE.VALUE( ‘LOGIN_ID ‘)) FROM dual
SELECT FND_PROFILE.VALUE(‘ORG_ID’) FROM dual
SELECT FND_PROFILE.VALUE(‘SO_ORGANIZATION_ID’) FROM dual
SELECT FND_PROFILE.VALUE(‘USERNAME’) FROM dual
SELECT FND_PROFILE.VALUE(‘GL_SET_OF_BKS_ID’) FROM dual

Core HR Basic Concepts

 



What is Human Resources Management?
Any successful enterprise needs to search for new ways to streamline the processes. One of the important aspects of any enterprise is Workforce. This is nothing but the labor force or personnel.
Human Resources Management is used for addressing the employees’ aspect of this continuous improvement. It helps the enterprises in reviewing and improving their business functions related to personnel management.

Oracle Human Resources – Salient features

·         Manage the entire recruitment cycle
·         Design organizational models that match current and future business strategies and objectives
·         Perform position management by defining and recording required skills, competencies, experience, and qualifications for positions, jobs, and organizations
·         Perform career management functions relating to the definition of competencies, assessments, suitability matching, graphical ranking, and succession planning
·         Administer and maintain benefits plans, coverage levels, and contribution allocations
·         Manage salary proposals and approve these by component

Work Structures


Work structures represent the different ways in which employees can work within the enterprise. These are the frameworks for defining the assignments for the employees. These can be also understood as the representation of the units within the enterprise. The Business Group is the largest unit and represents the enterprise as a whole.
Work structures include internal organizations (e.g. departments, divisions etc.), payrolls, jobs, positions, grades, grading structures and the employee grouping used in the enterprise.

People

People are the nucleus of any enterprise. Oracle HRMS gives the ability to hold information about the current employees, ex-employees, applicants, contacts, and contingent workers. Along with the standard information, the system can store the information about the addresses, phones, nationality, qualifications, absence history, interview records, etc. Special information can be defined to store any other information for the employees. E.g. special information could be languages known or hobbies. Work-related information such as the preference of working hours (shifts), hours of work could also be maintained.

Assignments

Assignment for an employee is nothing but the employee’s place within the enterprise. The assignment gives information about the organization for which the employee works. The details about the employee’s location, job, role, position, grade, location, payroll, supervisor, working hours and loads of other information can also be obtained from assignment data. As and when the information about the employee assignment is changed, work history is automatically built up for the employee.
Assignments are also used to group people for reporting, managing and costing purposes. Compensation benefits planning and administration could also be done with the help of the assignments.

Date Tracking


Typical data that changes for any enterprise includes corporate restructuring, employee development-related changes, departmental changes etc. Using Oracle HRMS, the changes can be effectively managed without the need of redefining of the parts.
These changes could be either Dated or Date Tracked.
Dated
Work Structures-related data is dated. Dates can be attached to a work structure to maintain the versions ad the structure changes. The previous structure can be maintained as historical data and a new version can be created with the future dates. In Oracle HRMS, the information related to organizations, jobs, grades, locations is dated. The work structure is active as of a Date From, which is entered while defining it. It remains valid till the Date To. A work structure cannot be assigned to an employee before the Date From or after the Date To.
Date Tracked
The dynamic information related to the employees, employee assignments, compensations and benefits is date tracked. Date tracking is a feature available in HRMS, which is useful in maintaining a history of the data when any changes are made. To view the date tracked data from the front end, an effective start date needs to be set. The setting of effective date ensures that the data effective only of that date is available for the users to view, change, validate or report. When any changes are done to the data, the user can choose whether the change would be a correction to the last changes or a new update that needs to be recorded in the history. The Set effective date option is available on the screens which deal with the date-tracked data. This is discussed in detail in the technical section for date tracking.

Organization

Navigation: Work Structures Ã  Organization Ã  Description
Business Group
In Oracle HRMS, the enterprise needs to be represented as an employer. This can be done by creating Business Group. Business Group is the largest organizational unit that is set up. Depending on the need, a single business group or multiple business groups can be set up. The defaults entered for a business group are the information that will generally apply to the people entered for that particular business group. These defaults can be overridden.
While setting up the business group, the following information needs to be set up:
·         Employee Number Generation for Employees and Applicants (Manual/Automatic)
·         National Identifier Generation only for Employees (Manual/Automatic)
·         Key flexfield structures that would be used in the business group.
·         Legislation code and default currency
Government Reporting Entity
This is available for selected legislations e.g. US. One more GREs can be set up within a business group. GRE organization is recognized as an employer by the government.
Representing Organizations
All the levels of an enterprise can be represented using Internal Organizations and External Organizations.
Internal Organizations are the departments, divisions or branches where the employees work.
External Organizations could be the hiring agencies, contractors, training institutes etc.
Location
The locations need to be set up before an organization can be defined. This is the exact location of the organization along with the address and phone number.

Navigation: Work Structures Ã  Location


Organization Classifications

This describes the purpose and use of the organization. The organization must be classified as an HR Organization to assign employees. 



















Tables
·         HR_LOCATIONS_ALL
·         HR_ALL_ORGANIZATION_UNITS – LOCATION_ID is linked with HR_LOCATIONS_ALL.LOCATION_ID
·         HR_ORGANIZATION_INFORMATION – Stores the classification and the business group details. ORGANIZATION_ID is linked with HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID

Note: The data stored in above details is for a business group. In case of a Global implementation, data for all the business groups will be stored in these tables.
The field business_group_id stores the id of the business group.

Jobs

Navigation: Work Structures Ã  Job Ã  Description
An employee’s primary role is set up using a job. It is a generic role within the business group that is independent of the organization. E.g. manager's job can be present in any organization.
Job Name is a key flexfield. Each job is held in a job group. A job group stores the jobs of similar types. All the jobs defined in HRMS for employees must be a part of the default HR Job Group.







Job Group Navigation: Work Structures Ã  Job Ã  Job Group























Tables
·         PER_JOB_DEFINITIONS – Stores the segment of the KFF. This is linked to fnd tables that store the KFF structure details.
·         PER_JOB_GROUPS
·         PER_JOBS – JOB_DEFINITION_ID is linked with PER_JOB_DEFINITIONS.JOB_DEFINITION_ID. JOB_GROUP_ID is linked with PER_JOB_GROUPS.JOB_GROUP_ID.

Note: The data stored in above details is for a business group. In case of a Global implementation, data for all the business groups will be stored in these tables.
The field business_group_id stores the id of the business group.

Position

Navigation: Work Structures Ã  Position Ã  Description
Position is a specific occurrence of a job. Position is fixed within an organization. E.g. Sales Manager is an occurrence of the job Manager in the Sales Organization. Similarly, HR Manager will be one more occurrence of the same job in the HR Organization. There could be one or many holders of the position in an organization. This is a key flexfield.
A Position is defined for an Organization and a Job. Positions data is date tracked.































Note: The data stored in above details is for a business group. In case of a Global implementation, data for all the business groups will be stored in these tables.
The field business_group_id stores the id of the business group.

Grade

Navigation: Work Structures Ã  Grade Ã  Description

Grades are used to decide the relative status of employee assignments. Compensation and Benefits are also decided based on the grades. E.g. Salary, company cell phone, company car – decided based on the grades. This is a key flexfield.
Valid grades could be defined for a job or for a position. A grade could be a valid grade either for a job or for a position. A single grade cannot be valid one for both a job and a position.





How to open Periods in Oracle Apps

 


To Create  sales order,  for a given month(period), that  month  should be opened in  five Modules.
·         GL-> Setup-> Open /Close.
·         AP-> Accounting ->Control Payable Periods.
·         PO->Setup->Financials-> Accounting->Open/Close Periods.
·         INV->Accounting Close Cycle ->Inventory Accounting Period->(Selct Org )
·         AR->Control ->Accounting ->Open/Close  Periods.

Query to find AP Supplier Remittance Email Addresses

 


The following query will display all the active vendors' email addresses by their associated sites. This will work only in Oracle R12.


SELECT
       -- s.vendor_id,
       -- st.vendor_site_id,
       -- s.party_id,
       -- st.party_site_id,
       s.vendor_name              "Vendor Name",
       s.segment1                 "Vendor Number",
       s.vendor_type_lookup_code  "Vendor Type",
       st.vendor_site_code        "Vendor Site Code",
       ou.name                    "Operating Unit",
       --
       iepa.remit_advice_delivery_method  "Remittance Delivery Method",
       iepa.remit_advice_email            "Remittance Advice Email"
  FROM
       ap.ap_suppliers              s,
       ap.ap_supplier_sites_all     st,
       hr_operating_units           ou,
       iby.iby_external_payees_all  iepa
 WHERE
       1=1
   --
   -- AND s.vendor_type_lookup_code = 'EMPLOYEE'
   --
   AND TRUNC (SYSDATEBETWEEN TRUNC (s.start_date_active) AND TRUNC (NVL (s.end_date_active, SYSDATE+1))
   AND s.enabled_flag = 'Y'
   --
   AND iepa.supplier_site_id = st.vendor_site_id
   AND iepa.payee_party_id = s.party_id
   --
   AND st.org_id = ou.organization_id
   AND st.vendor_id = s.vendor_id
   --
 ORDER BY s.vendor_name, st.vendor_site_code;

Types of animations

Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...