Profile Value at Various Levels

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('FND%Diag%')--UPPER ('%&v_profile%')
AND      pot.profile_option_name = b.profile_option_name
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      (   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

External Table in Oracle

External Table in Oracle

Oracle has come with a new feature facilitating us to query directly from the FLAT File. This is know as external tables.

Steps:
1) Create Directory

create or replace directory abhi_ext_dir as '/home/abhi/external_table'

2) Grant access to Directory

grant read, write on directory abhi_ext_dir to apps

The user must have a read and write permission the directory('/home/abhi/external_table' in this case).

3) Create External Table

CREATE TABLE abhi_external_Table ( col1 VARCHAR2(50) ,col2 VARCHAR2(50) )
ORGANIZATION EXTERNAL
( TYPE oracle_loader DEFAULT DIRECTORY abhi_ext_dir ACCESS PARAMETERS ( FIELDS TERMINATED BY '|' ) LOCATION ('abc.txt'))

abc.txt is the name of the file that will be located in the path mentioned in the directory abhi_ext_dir

4) Place the file in the Directory Path

abc.txt (used in above create table command) is copied the directory.


5) Thats it, query table to extract data.

SELECT * FROM abhi_external_table

Everytime the file is updated with new data the data in the table will be automatically refreshed.

Ex.


--External Table


create or replace directory abhi_ext_dir as '/tmp'

--grant read, write on directory abhi_ext_dir to apps;


cat xyz.txt

56november, 15, 1980  baker             mary       alice     09/01/2004
87december, 20, 1970  roper             lisa       marie     01/01/1999


CREATE TABLE emp (emp_no CHAR(6), last_name CHAR(25), first_name CHAR(20), middle_initial CHAR(1), hire_date DATE, dob DATE);

CREATE TABLE emp_load
  (employee_number      CHAR(5),
   employee_dob         CHAR(20),
   employee_last_name   CHAR(20),
   employee_first_name  CHAR(15),
   employee_middle_name CHAR(15),
   employee_hire_date   DATE
  )
ORGANIZATION EXTERNAL
  (TYPE ORACLE_LOADER
   DEFAULT DIRECTORY abhi_ext_dir --def_dir1
   ACCESS PARAMETERS
     (
      RECORDS DELIMITED BY NEWLINE
      FIELDS (employee_number      CHAR(2),
              employee_dob         CHAR(20),
              employee_last_name   CHAR(18),
              employee_first_name  CHAR(11),
              employee_middle_name CHAR(11),
              employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"
             )
     )
   LOCATION ('xyz.txt')
  );


select * from emp_load

drop table emp_load;

INSERT INTO emp (emp_no,
                 first_name,
                 middle_initial,
                 last_name,
                 hire_date,
                 dob)
(SELECT employee_number,
        employee_first_name,
        substr(employee_middle_name, 1, 1),
        employee_last_name,
        employee_hire_date,
        to_date(employee_dob,'month, dd, yyyy')
FROM emp_load);


SELECT * FROM emp;

  
select * from USER_EXTERNAL_TABLES;


drop table emp_load

--Other Commands


cat abc.txt
 123456, JIRI, 444-555-6666
 234567, TOM, 555-666-5555
 345678, CHRIS, 111-222-3333


CREATE TABLE abhi_external_table
 (
 CUSTOMER_ID     NUMBER(6),
 CUSTOMER_NAME   VARCHAR2(60),
 CUSTOMER_SSN    VARCHAR2(60)
 )
 ORGANIZATION EXTERNAL
    (
        TYPE oracle_loader
        DEFAULT DIRECTORY abhi_ext_dir
        ACCESS PARAMETERS ( FIELDS TERMINATED BY ',' )
        LOCATION ('abc.txt')
    )


select * from abhi_external_table
----------------------------------------
CUSTOMER_ID    CUSTOMER_NAME    CUSTOMER_SSN
123456     JIRI     444-555-6666
234567     TOM     555-666-5555
345678     CHRIS     111-222-3333

Concurrent Program

Low and High Date range parameter in the concurrent program

The concurrent program has 2 date parameter e.g. Start Date and End date. The requirement is that start date should always be less than end date.

Solution:
1) In the concurrent program definition parameters, select Range as Low for Start Date. See Screenshot below
2) In the concurrent program definition parameters, select Range as high for End Date. See Screenshot below. Note that the value set being used for Start date and End date should be same.

3) Test program entering incorrect value for the parameter.

4) Test again by entering correct value.

R12 GL SCRIPTS

--GL QUERIES R12


SELECT * FROM GL.GL_JE_BATCHES WHERE NAME LIKE 'AAGRA7_BATCH Payables A 204981 19501478';

SELECT * FROM GL_JE_HEADERS WHERE JE_BATCH_ID = 210086;

SELECT * FROM GL_JE_LINES WHERE JE_HEADER_ID = 263237;

SELECT * FROM GL_IMPORT_REFERENCES WHERE JE_BATCH_ID = 210086;

select * from GL_BALANCES;

SELECT * FROM AP.AP_TRIAL_BALANCE WHERE INVOICE_ID=1181871;

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME ='AP_TRIAL_BALANCE';

SELECT * FROM GL.GL_LEGAL_ENTITIES_BSVS;

SELECT * FROM GL.GL_LEDGERS;

SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE '%LEGAL%ENTITY%';

SELECT * FROM GL_LEDGER_SETS_V;


SELECT * FROM GL_TRANSLATION_RATES WHERE SET_OF_BOOKS_ID=4 AND PERIOD_NAME LIKE '%12';

--************************************************

SELECT * FROM GL_CONSOLIDATION where name like 'INR TO INR(US GAAP)';

SELECT * FROM GL_CONSOLIDATION_HISTORY WHERE CONSOLIDATION_ID=1007 AND FROM_PERIOD_NAME='MAY-12';

SELECT * FROM GL_CONS_BATCHES where JE_BATCH_ID=210146;

select * from GL_JE_BATCHES where name like '03-MAY-12 INR TO INR(US GAAP) Consolidation A 205020 %';

SELECT * FROM GL_JE_BATCHES WHERE JE_BATCH_ID=210146;


--*******************************************************************

R12 AP SQLS

select * from ap_invoices_all where invoice_num like '25-apr-2012';

select * from AP_INVOICE_LINES_ALL where invoice_id=1181871;

select * from AP_INVOICE_DISTRIBUTIONS_ALL where invoice_id=1181871;

SELECT * FROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID=1181871;

select * from AP_PAYMENT_SCHEDULES_ALL where INVOICE_ID=1181871;

--SELECT * FROM AP_ACCOUNTING_EVENTS_ALL WHERE SOURCE_TABLE='AP_INVOICES' AND SOURCE_ID=1181871;

SELECT * FROM XLA.XLA_TRANSACTION_ENTITIES WHERE SOURCE_ID_INT_1='1181871';

SELECT * FROM XLA.XLA_TRANSACTION_ENTITIES WHERE SOURCE_ID_INT_1='1181871' AND ENTITY_CODE='AP_INVOICES';
--TRANSACTION_NUMBER => INVOICE_NUMBER
-- LEDGER_ID
--GET THE EVENT_ID= 4590221

SELECT * FROM XLA.XLA_EVENTS WHERE ENTITY_ID=4590221;
--=> AFTER DRAFT ACCOUNTING
--GET THE EVENT_ID- 4623169
--EVENT_STATUS_CODE U => UNPOSTED
--PROCESS_STATUS_CODE - D => DRAFT
--EVENT_TYPE_CODE
--=> AFTER FINAL ACCOUNTING
--EVENT_STATUS_CODE U => P
--PROCESS_STATUS_CODE - P

SELECT * FROM XLA.XLA_AE_HEADERS WHERE ENTITY_ID=4590221 AND EVENT_ID=4623169;
--=> AFTER DRAFT ACCOUNTING
--ACCOUNTING_ENTRY_STATUS_CODE F
--=> AFTER FINAL ACCOUNTING
--ACCOUNTING_ENTRY_STATUS_CODE F

SELECT * FROM XLA.XLA_AE_LINES WHERE AE_HEADER_ID=8489753;
-- AE LINES WHEN DRAFT

SELECT * FROM XLA.XLA_AE_LINES WHERE AE_HEADER_ID=8489758;
-- AE LINES WHEN FINAL

--AE HEADER ID IS CHANGED AFTER ACCOUTNING TO FINAL



-----------------------------------
--New R12 tables  -> Old 11i Tables
--AP_SUPPLIERS - replaces PO_VENDORS
--AP_SUPPLIER_SITES_ALL- replaces PO_VENDOR_SITES_ALL

SELECT * FROM AP_SUPPLIERS
WHERE VENDOR_ID=1193;
--> GET PARTY ID FROM HERE 160297

SELECT * FROM AP_SUPPLIER_SITES_ALL
WHERE VENDOR_ID=1193 AND VENDOR_SITE_ID=57459;
--> GET PATRY SITE ID FROM HERE

--Additional supplier related tables in IBY(Payments) and HZ(TCA): IBY_EXTERNAL_PAYEES_ALL - stores Payee(supplier) information.
--HZ_PARTIES - Party data for the suppliers.
--HZ_PARTY_SITES - Party site data for the supplier sites.

SELECT * FROM HZ_PARTIES WHERE PARTY_ID=160297;

SELECT * FROM  HZ_PARTY_SITES WHERE PARTY_ID =160297 AND PARTY_SITE_ID=60405;

SELECT * FROM IBY_EXTERNAL_PAYEES_ALL WHERE PAYEE_PARTY_ID=160297 AND PARTY_SITE_ID=60405;


--Invoices
--Additional table in R12: AP_INVOICE_LINES_ALL
--Allocations - ap_chrg_allocations_all is obsolete in R12
--Taxes
--Functionality provided by E-Business Tax
--New tables in R12
--ZX_LINES - Detailed Tax lines for the invoice (trx_id = invoice_id)
--ZX_LINES_SUMMARY - Summary tax lines for the invoice (trx_id = invoice_id)
--ZX_REC_NREC_DIST  - Tax distributions for the invoice (trx_id = invoice_id)
--ZX_LINES_DET_FACTORS - Tax determination factors for the invoice (trx_id = invoice_id)

--Payments
--Functionality moved to central Payments (IBY)

--New IBY tables in R12:
--IBY_PAY_SERVICE_REQUESTS  - Payment Process Request information (11i Terminology is  Payment Batch)
--IBY_PAY_INSTRUCTIONS_ALL - Payment Instruction information
--IBY_DOC_PAYABLES_ALL - Invoice information stored by IBY for generating payment
--IBY_PAYMENTS_ALL - Payment Information

--The following tables are used in PPR in R12 . These were used in 11i for payment batches also.
--AP_INV_SELECTION_CRITERIA_ALL
--AP_SELECTED_INVOICES_ALL

--The following AP tables are still in use in R12 to store payment related information.
--AP_INVOICE_PAYMENTS_ALL
--AP_CHECKS_ALL
--AP_PAYMENT_HISTORY_ALL


--Accounting
--Functionality moved to SubLedger Accounting (SLA)
--New R12 tables:
--XLA_EVENTS -> replaces AP_ACOCUNTING_EVENTS_ALL
--XLA_AE_HEADERS -> replaces AP_AE_HEADERS_ALL
--XLA_AE_LINES-> replaces AP_AE_LINES_ALL
--XLA_DISTRIBUTION_LINKS

--Trial Balance
--New R12 Table
--XLA_TRIAL_BALANCES

--AP_LIABILITY_BALANCE-> not used in new R12 transactions
--AP_TRIAL_BALANCE -> not used in new R12 transactions


--Bank Accounts
--Functionality moved to Cash Management.

--CE_BANK_ACCOUNTS -> replaces AP_BANK_ACCOUNTS_ALL
--CE_BANK_ACCT_USES_ALL  -> replaces AP_BANK_ACCOUNT_USES_ALL
--CE_PAYMENT_DOCUMENTS -> AP_CHECK_STOCKS_ALL

WEB ADI - DESKTOP INTEGRATION FRAMEWORK-3