Employee and Supervisor Details

Using the below script we can find the Active Employee, Assignments and his Supervisor details.
Employee and Supervisor Details

   
SELECT fu.user_name,
       per.full_name,
       per.employee_number,
       per1.full_name supervisor_name,
       per1.employee_number supervisor_number,
       gcc.concatenated_segments Supervisor_default_expense_account
  FROM fnd_user fu,
       per_all_people_f per ,
       per_all_assignments_f pf ,
       per_all_people_f per1,
       per_all_assignments_f pf1,
       gl_code_combinations_kfv gcc
 WHERE fu.employee_id = per.person_id
   AND SYSDATE BETWEEN per.effective_start_date AND per.effective_end_date
   AND SYSDATE BETWEEN pf.effective_start_date AND pf.effective_end_date
   AND pf.person_id = per.person_id
   AND pf.supervisor_id = per1.person_id
   AND per1.person_id = pf1.person_id
   AND SYSDATE BETWEEN per1.effective_start_date AND per1.effective_end_date
   AND SYSDATE BETWEEN pf1.effective_start_date AND pf1.effective_end_date
   AND pf1.default_code_comb_id = gcc.code_combination_id

Form Functions attached to a Responsibility

Using the below Query we can get all the form functions that are attached to a Responsibility including Sub Menus and Excluding Menu Exclusions.
   
SELECT frtl.responsibility_name,
       fr.responsibility_key,
       fm.menu_id,
       fm.menu_name,
       menu.function_id,
       menu.prompt,
       fffv.user_function_name,
       fffv.function_name,
       fffv.TYPE
  FROM (SELECT connect_by_root fmet.menu_id top_menu_id,
               fmet.menu_id                 menu_id,
               fmet.sub_menu_id,
               fmet.function_id,
               fmet.prompt
          FROM fnd_menu_entries_vl fmet
        CONNECT BY PRIOR fmet.sub_menu_id = fmet.menu_id
                         AND PRIOR fmet.prompt IS NOT NULL) menu,
       fnd_responsibility fr,
       fnd_responsibility_tl frtl,
       fnd_menus fm,
       fnd_form_functions_vl fffv
 WHERE fr.menu_id = menu.top_menu_id
   AND fffv.function_id = menu.function_id
   AND fffv.TYPE <> 'SUBFUNCTION'
   AND menu.function_id IS NOT NULL
   AND menu.prompt IS NOT NULL
   AND fm.menu_id = menu.menu_id
   AND frtl.responsibility_id = fr.responsibility_id
   AND frtl.responsibility_name LIKE 'System Administrator'
   AND menu.function_id NOT IN (SELECT ffvl.function_id
                                  FROM apps.fnd_resp_functions frf,
                                       applsys.fnd_responsibility_tl frt,
                                       apps.fnd_form_functions_vl ffvl
                                 WHERE
       frf.responsibility_id = frt.responsibility_id
                                   AND frf.action_id = ffvl.function_id
                                   AND frf.rule_type = 'F'
                                   AND
           frt.responsibility_name = frtl.responsibility_name)
   AND menu.menu_id NOT IN (SELECT fmv.menu_id
                              FROM apps.fnd_resp_functions frf,
                                   applsys.fnd_responsibility_tl frt,
                                   apps.fnd_menus_vl fmv
                             WHERE
       frf.responsibility_id = frt.responsibility_id
                               AND frf.action_id = fmv.menu_id
                               AND frf.rule_type = 'M'
                               AND
       frt.responsibility_name = frtl.responsibility_name)
 ORDER BY fffv.user_function_name;

Useful Month End Process Queries – Operations

1. Transaction Struck in Material Transactions Open Interface:
Transaction in Material Transaction Open Interface
   
SELECT COUNT(1), mp.organization_code
  FROM mtl_transactions_interface_v mtiv,
       mtl_parameters mp
 WHERE 1 = 1
   AND mtiv.organization_id=mp.organization_id
   AND (process_flag = 3 or lock_flag = 1)
 GROUP BY mp.organization_code;

2. Pending Material Transactions:
Pending Material Transactions
   
SELECT COUNT(1), mp.organization_code
  FROM mtl_transactions_temp_all_v mtta,
       mtl_parameters mp
 WHERE 1 = 1
   AND mtta.organization_id = mp.organization_id
 GROUP BY mp.organization_code;

3. Pending Move Transactions(WIP):
Pending Move Transactions WIP

   
SELECT COUNT(1), mp.organization_code
  FROM wip_move_txn_interface_v wmt,
       mtl_parameters mp
 WHERE wmt.organization_id = mp.organization_id
 GROUP BY mp.organization_code;

4. Pending WIP uncosted:
Pending WIP Uncosted

   
SELECT COUNT(1), mp.organization_code
  FROM wip_cost_txn_interface_v wct,
       mtl_parameters mp
 WHERE wct.organization_id = mp.organization_id
 GROUP BY mp.organization_code;

5. Error Costed Transactions:
Error Costed Transactions
   
SELECT COUNT(1), mp.organization_code
  FROM mtl_material_transactions mmt,
       mtl_parameters mp
 WHERE mmt.organization_id=mp.organization_id
   AND costed_flag = 'E'
 GROUP BY mp.organization_code;

6. Not Costed Transactions:
Not Costed Transactions

   
SELECT COUNT(1), mp.organization_code
  FROM mtl_material_transactions mmt,
       mtl_parameters mp
 WHERE mmt.organization_id = mp.organization_id
   AND costed_flag = 'N'
 GROUP BY mp.organization_code;

7. Pending Receiving Transactions:
Pending Receiving Transactions
   
SELECT COUNT(1), mp.organization_code
  FROM rcv_transactions_interface rti,
       mtl_parameters mp
 WHERE rti.to_organization_id = mp.organization_id
   AND rti.processing_status_code = 'PENDING'
 GROUP BY mp.organization_code;

8: unprocessed shipping transactions:
unprocessed shipping transactions
   
SELECT COUNT(1), mp.organization_code
  FROM wsh_delivery_details     wdd
      ,oe_order_headers_all     ooh
      ,oe_order_lines_all       ool
      ,wsh_delivery_assignments wda
      ,wsh_new_deliveries       wnd
      ,mtl_parameters           mp
 WHERE wdd.source_header_id = ooh.header_id
   AND wdd.source_line_id = ool.line_id
   AND ooh.header_id = ool.header_id
   AND wdd.delivery_detail_id = wda.delivery_detail_id
   AND wda.delivery_id = wnd.delivery_id
   AND wdd.source_code = 'OE'
   AND wdd.released_status = 'C'
   AND wdd.inv_interfaced_flag IN ('N', 'P')
   AND wnd.status_code IN ('CL', 'IT')
   AND wdd.organization_id =mp.organization_id
 GROUP BY mp.organization_code;

9. Pending or Error Transactions:
Pending or Error transactions
   
SELECT transaction_id
      ,consumption_processed_flag
      ,ERROR_CODE
      ,net_qty
      ,batch_id
      ,creation_date
      ,consumption_release_id
      ,consumption_po_header_id
 FROM mtl_consumption_transactions
WHERE consumption_processed_flag IN ('E', 'N');

10. Unprocessed Shipping Transactions:
Unprocessed shipping Transactions
   
SELECT b.organization_code,
       b.organization_id  ,
       b.operating_unit   ,
       count(*)
  FROM wsh_delivery_details     wdd
      ,oe_order_headers_all     ooh
      ,oe_order_lines_all       ool
      ,wsh_delivery_assignments wda
      ,wsh_new_deliveries       wnd
      ,wsh_delivery_legs        wdl
      ,wsh_trip_stops           wts
      ,org_organization_definitions b
 WHERE wdd.source_code = 'OE'
   AND wdd.released_status = 'C'
   AND wdd.inv_interfaced_flag IN ('N', 'P')
   AND wdd.organization_id = b.organization_id
   AND wdd.delivery_detail_id = wda.delivery_detail_id
   AND wda.delivery_id = wnd.delivery_id
   AND wnd.status_code IN ('CL', 'IT')
   AND wdl.delivery_id         = wnd.delivery_id
   AND wdl.pick_up_stop_id     = wts.stop_id
 GROUP BY b.organization_code,
          b.organization_id  ,
          b.operating_unit;

Create Accounting or SLA Exception Queries

AR Create Accounting Errors / AR SLA Errors
   
SELECT led.name ledger_name,
       ae.encoded_msg,
       xe.event_type_code,
       xe.event_status_code,
       xe.process_status_code,
       xte.entity_code,
       hro.name ou_name,
       xte.transaction_number,
       xe.event_date,
       xte.entity_code,
       xte.source_id_int_1
  FROM xla_events xe,
       xla_accounting_errors ae,
       xla.xla_transaction_entities xte,
       gl_ledgers led,
       hr_operating_units hro
 WHERE ae.application_id = 222
   AND led.ledger_id = xte.ledger_id
   AND ae.event_id = xe.event_id
   AND xte.entity_id = xe.entity_id
   AND xte.ledger_id = ae.ledger_id
   AND hro.organization_id(+) = xte.security_id_int_1;

Payables Create Accounting Errors:
AP SLA Errors
   
SELECT led.name  ledger_name,
       ae.encoded_msg,
       xe.event_type_code,
       xe.event_status_code,
       xe.process_status_code,
       xte.entity_code,
       hro.name  OU_NAME,
       xte.transaction_number ,
       xe.event_date,
       xte.entity_code,
       xte.source_id_int_1
  FROM XLA_EVENTS xe,
       XLA_ACCOUNTING_ERRORS ae,
       xla.XLA_TRANSACTION_ENTITIES xte,
       GL_LEDGERS led,
       HR_OPERATING_UNITS hro
 WHERE ae.application_id = 200
   AND led.ledger_id = xte.ledger_id
   AND ae.event_id = xe.event_id
   AND xte.entity_id = xe.entity_id
   AND xte.ledger_id = ae.ledger_id
   AND hro.organization_id(+) = xte.security_id_int_1;

FA Create Accounting Errors:
FA SLA Errors
   
SELECT led.name ledger_name,
       ae.encoded_msg,
       xe.event_type_code,
       xe.event_status_code,
       xe.process_status_code,
       xte.entity_code,
       hro.name OU_NAME,
       xte.transaction_number ,
       xe.event_date,
       xte.entity_code,
       xte.source_id_int_1
  FROM XLA_EVENTS xe,
       XLA_ACCOUNTING_ERRORS ae,
       xla.XLA_TRANSACTION_ENTITIES xte,
       GL_LEDGERS led,
       HR_OPERATING_UNITS hro
 WHERE ae.application_id = 140
   AND led.ledger_id = xte.ledger_id
   AND ae.event_id = xe.event_id
   AND xte.entity_id = xe.entity_id
   AND xte.ledger_id = ae.ledger_id
   AND hro.organization_id(+) = xte.security_id_int_1;

Cost Management Create Accounting Errors:
Cost Management SLA Errors
   
SELECT led.name ledger_name,
       ae.encoded_msg,
       xe.event_type_code,
       xe.event_status_code,
       xe.process_status_code,
       xte.entity_code,
       hro.name  ORG_NAME,
       xte.transaction_number ,
       xe.event_date,
       xte.entity_code,
       xte.source_id_int_1
  FROM xla_events xe,
       xla_accounting_errors ae,
       xla.xla_transaction_entities xte,
       gl_ledgers led,
       hr_all_organization_units hro
 WHERE ae.application_id = 707
   AND led.ledger_id = xte.ledger_id
   AND ae.event_id = xe.event_id
   AND xte.entity_id = xe.entity_id
   AND xte.ledger_id = ae.ledger_id
   AND hro.organization_id(+) = xte.security_id_int_1 ;

Channel Revenue Management Create Accounting Errors:
OCRM SLA Errors
   
SELECT led.name   ledger_name,
       oca.claim_number       "CLAIM_NUMBER/OFFER_CODE",
       NULL                   OFFER_NAME,
       ae.encoded_msg,
       xe.event_type_code,
       xe.event_status_code,
       xe.process_status_code,
       xte.entity_code,
       hro.name               OU_NAME,
       xte.transaction_number "UTILIZATION_ID/CLAIM_ID",
       xe.event_date,
       oca.creation_date      "CLAIM/accrual_date",
       oca.acctd_amount       Amount,
       oca.currency_code      Currency_code,
       xte.entity_code
  FROM XLA_EVENTS xe,
       XLA_ACCOUNTING_ERRORS ae,
       xla.XLA_TRANSACTION_ENTITIES xte,
       GL_LEDGERS led,
       HR_OPERATING_UNITS hro,
       OZF_CLAIMS_ALL oca
 WHERE ae.application_id = 682
   AND led.ledger_id = xte.ledger_id
   AND ae.event_id = xe.event_id
   AND xte.entity_id = xe.entity_id
   AND xte.ledger_id = ae.ledger_id
   AND hro.organization_id(+) = xte.security_id_int_1
   AND xte.entity_code = 'CLAIM_SETTLEMENT'
   AND oca.claim_id = xte.source_id_int_1
UNION ALL
SELECT led.name               ledger_name,
       oz.name,
       oz.description         OFFER_NAME,
       ae.encoded_msg,
       xe.event_type_code,
       xe.event_status_code,
       xe.process_status_code,
       xte.entity_code,
       hro.name               OU_NAME,
       xte.transaction_number "UTILIZATION_ID/CLAIM_ID",
       xe.event_date,
       ou.creation_date       "CLAIM/accrual_date",
       ou.plan_curr_amount    Amount,
       ou.plan_currency_code  Currency_code,
       xte.entity_code
  FROM XLA_EVENTS xe,
       XLA_ACCOUNTING_ERRORS ae,
       xla.XLA_TRANSACTION_ENTITIES xte,
       GL_LEDGERS led,
       HR_OPERATING_UNITS hro,
       OZF_FUNDS_UTILIZED_ALL_B ou,
       OZF_OFFERS_V oz
 WHERE ae.application_id = 682
   AND led.ledger_id = xte.ledger_id
   AND ae.event_id = xe.event_id
   AND xte.entity_id = xe.entity_id
   AND xte.ledger_id = ae.ledger_id
   AND hro.organization_id(+) = xte.security_id_int_1
   AND ou.plan_id = oz.list_header_id
   AND ou.utilization_id = xte.source_id_int_1
   AND xte.entity_code = 'ACCRUAL';

Inventory On-hand quantity Interface

USING this interface we can UPDATE ON hand quantity OF Inventory Item.
Interface Tables:
MTL_TRANSACTIONS_INTERFACE
MTL_MTL_TRANSACTION_LOTS_INTERFACE (IF the item IS Lot controlled)
MTL_SERIAL_NUMBERS_INTERFACE (IF the item IS Serial controlled)

Concurrent Program: Launch the Transaction Manager through Interface Manager
OR explicitly CALL the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS ()
TO launch a dedicated transaction worker TO process them.
The Transaction Manager picks up the rows TO process based ON the LOCK_FLAG,  TRANSACTION_MODE,AND PROCESS_FLAG.
Only recordsWITH TRANSACTION_MODE OF 3,  LOCK_FLAG OF ’2′,AND PROCESS_FLAG OF ’1′ will be picked up BY the Transaction Manager
AND assigned TO a Transaction Worker.
IF a record fails TO process completely,  THEN PROCESS_FLAG will be  SET TO ’3′ AND ERROR_CODE AND ERROR_EXPLANATION will be populated
WITH the cause FOR the error.

Base Tables:
MTL_ON_HAND_QUANTITIES
MTL_LOT_NUMBERS
MTL_SERIAL_NUMBERS

Validations:

 VALIDATE organization_id · CHECK IF item IS assigned TO organization ·

VALIDATE disposition_id · CHECK IF the item FOR the org IS lot controlled before inserting INTO the Lots interface table. · CHECK IF the item FOR the org IS serial controlled before inserting
INTO Serial interface table. · CHECK IF inventory already EXISTS FOR that item IN that org AND FOR a lot. ·
VALIDATE organization_id,  organization_code. · VALIDATE inventory item id.

Transaction period must be open.

SOME important columns that need TO be populated IN the interface tables:
MTL_TRANSACTIONS_INTERFACE:
TRANSACTION_SOURCE_NAME (ANY USER DEFINED VALUE),
 TRANSACTION_HEADER_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL) TRANSACTION_INTERFACE_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL – IF item IS lot
OR serial controlled, use this field TO LINK TO mtl_transactions_interface otherwise leave it AS NULL),
  TRANSACTION_DATE,
  TRANSACTION_TYPE_ID,
  PROCESS_FLAG (1        = Yet TO be processed, 2 = Processed, 3= Error) TRANSACTION_MODE (2 = Concurrent – TO launch a dedicated transaction worker TO explicitly process a
  SET OF transactions. 3 = Background – will be picked up BY transaction manager polling process
AND assigned TO transaction worker. These will NOT be picked up until the transaction manager IS running) SOURCE_CODE,
  SOURCE_HEADER_ID,
  SOURCE_LINE_ID (Details about the source LIKE ORDER Entry etc FOR tracking purposes) TRANSACTION_SOURCE_ID Source Type FOREIGN KEY REFERENCE Account GL_CODE_COMBINATIONS.CODE_COMBINATION_ID Account ALIAS MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID Job
OR SCHEDULE WIP_ENTITIES.WIP_ENTITY_ID Sales ORDER MTL_SALES_ORDERS.SALES_ORDER_ID ITEM_SEGMENT1 TO 20,
  TRANSACTION_QTY,
  TRANSACTION_UOM,
  SUBINVENTORY_CODE,
  ORGANIZATION_ID,
  LOC_SEGMENT1 TO 20. MTL_TRANSACTION_LOTS_INTERFACE: TRANSACTION_INTERFACE_ID,
  LOT_NUMBER,
  LOT_EXPIRATION_DATE,
  TRANSACTION_QUANTITY,
  SERIAL_TRANSACTION_TEMP_ID (This IS required FOR items under both lot
AND serial control TO identify child records IN mtl_serial_numbers_interface) MTL_SERIAL_NUMBERS_INTERFACE: TRANSACTION_INTERFACE_ID,
  FM_SERIAL_NUMBER,
  TO_SERIAL_NUMBER,
  VENDOR_SERIAL_NUMBER Sample data FOR Mtl_Transactions_interface table.
 
 
 DECLARE
  l_source_code      VARCHAR2 (100) := 'MIGRATION';
  l_lock_flag        NUMBER         := 2;
  l_source_line_id   NUMBER         := 99;
  l_source_header_id NUMBER         := 99;
  l_process_flag     NUMBER         := 1;
  l_user_id          NUMBER         := 6871;
  --fnd_global.user_id;
  l_resp_id NUMBER := 53922;
  --fnd_global.resp_id;
  l_appl_id NUMBER := 7000;
  --fnd_global.resp_appl_id;
  l_org_id NUMBER := 3;
  --fnd_global.org_id;
  l_login_id NUMBER := 6871;
  --fnd_global.login_id;
  l_transaction_mode         NUMBER := 3;
  l_transaction_interface_id NUMBER;
BEGIN
  DBMS_OUTPUT.put_line ('Before inserting!');
  fnd_client_info.set_org_context ('1');
  --initializing org_id --fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id, l_login_id);
  fnd_global.apps_initialize (6871, 53922, 7000, 6871);
  SELECT mtl_material_transactions_s.NEXTVAL
  INTO l_transaction_interface_id
  FROM DUAL;
  INSERT
INTO mtl_transactions_interface
    (
      transaction_interface_id,
      source_code,
      source_line_id,
      source_header_id ,
      process_flag,
      validation_required,
      transaction_mode ,
      last_update_date,
      creation_date,
      created_by ,
      last_update_login,
      inventory_item_id,
      organization_id
      --
      ,
      transaction_quantity,
      transaction_uom,
      transaction_date ,
      subinventory_code,
      transaction_source_type_id ,
      transaction_action_id,
      transaction_type_id ,
      transaction_cost,
      distribution_account_id ,
      last_updated_by,
      lock_flag
    )
    VALUES
    (
      l_transaction_interface_id,
      l_source_code,
      l_source_line_id,
      l_source_header_id ,
      l_process_flag,
      1,
      l_transaction_mode ,
      SYSDATE,
      SYSDATE,
      l_user_id ,
      l_login_id,
      161073,
      l_org_id ,
      30,
      'NOS',
      SYSDATE,
      'FGS_OE',
      13,
      27,
      42,
      100,
      1001,
      l_user_id,
      l_lock_flag
    );
  COMMIT;
  DBMS_OUTPUT.put_line (Successfully Inserted data INTO Interface table!'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('ERROR WHILE INSERTING DATA INTO INTERFACE TABLE =' || SQLERRM); END;

FRM-92050: Failed to connect to Server: /forms/lservlet:

This issue only occurs in servlet mode and on IE version8.

Solution for the issue is.


Tools- Internet Options - Security - Custom Level - Enable XSS filter - Disable

This issue only occurs in servlet mode and on IE version8. Solution for the issue is. On Internet Explorer following navigation can be followed. Tools- Internet Options - Security - Custom Level - Enable XSS filter - Disable

Copy the BEST Traders and Make Money : http://bit.ly/fxzulu

Script to Show Period Statuses in GL, Purchasing, and Payables

To check the period status for AP & GL & PO via backend in oracle apps.
To pass the input as Set of Books ID

SELECT a.period_name,
  a.period_num,
  a.gl_status,
  b.po_status,
  c.ap_status
FROM
  (SELECT period_name,
    period_num,
    DECODE (closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never', closing_status) gl_status
  FROM gl_period_statuses
  WHERE application_id = 101
  AND start_date      >= '01-JAN-11'
  AND end_date         < '01-JAN-13'
  AND set_of_books_id  =
    &&set_of_books_id
  ) a,
  (SELECT period_name,
    DECODE (closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never', closing_status) po_status
  FROM gl_period_statuses
  WHERE application_id = 201
  AND start_date      >= '01-JAN-11'
  AND end_date         < '01-JAN-13'
  AND set_of_books_id  =
    &&set_of_books_id
  ) b,
  (SELECT period_name,
    DECODE (closing_status, 'O', 'Open', 'C', 'Closed', 'F', 'Future', 'N', 'Never', closing_status ) ap_status
  FROM gl_period_statuses
  WHERE application_id = 200
  AND start_date      >= '01-JAN-11'
  AND end_date         < '01-JAN-13'
  AND set_of_books_id  =
    &&set_of_books_id
  ) c
WHERE a.period_name = b.period_name
AND a.period_name   = c.period_name
ORDER BY a.period_num

XML Template and Data Definitions Table in Oracle Apps

To Find the Templates Name for XML Report
------------------------------------------
XDO_TEMPLATES_B
XDO_TEMPLATES_TL

To Find the Data Definitions for XML Report
--------------------------------------------
XDO_DS_DEFINITIONS_B
XDO_DS_DEFINITIONS_TL

To Find the Templates Name for XML Report XDO_TEMPLATES_B XDO_TEMPLATES_TL To Find the Data Definitions for XML Report XDO_DS_DEFINITIONS_B XDO_DS_DEFINITIONS_TL

Copy the BEST Traders and Make Money : http://bit.ly/fxzulu

To Find the Templates Name for XML Report XDO_TEMPLATES_B XDO_TEMPLATES_TL To Find the Data Definitions for XML Report XDO_DS_DEFINITIONS_B XDO_DS_DEFINITIONS_TL

Copy the BEST Traders and Make Money : http://bit.ly/fxzulu