TO CHECK THE ALERTS DETAILS:
---------------------

SELECT * FROM ALR_ALERTS

EXTERNAL_TABLE:
-----------------------
create or replace directory external_test as '/tmp';
create table test
(day_ varchar2(10),
 splr varchar2(20),
 wh__ varchar2(20),
 skps varchar2(20),
 val number)
organization external
(type oracle_loader default directory external_test
 access parameters
 (
  records delimited by newline
  fields terminated by whitespace
  (day_ char,
   splr char,
   wh__ char,
   skps char,
   val float external
  )
 )
 location ('test.txt')
)
reject limit unlimited;

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

CREATE TABLE all_objects_html
  ORGANIZATION EXTERNAL
    (
    TYPE ORACLE_DATAPUMP
    DEFAULT DIRECTORY xtern_data_dir
    LOCATION ( 'all_objects_xt.html' )
    )
  AS
    SELECT *
    FROM  all_objects;

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

CREATE TABLE External_Table_HR
(
  S_NUMBER  VARCHAR2(8 BYTE),
  NAME            VARCHAR2(100 BYTE),
  SEX             VARCHAR2(2 BYTE),
  DEPTID          VARCHAR2(6 BYTE),
  DEPT_DESCR      VARCHAR2(100 BYTE),
  GEO_LOCATION    VARCHAR2(100 BYTE),
  DESCR           VARCHAR2(100 BYTE),
  FULL_PART       VARCHAR2(1 BYTE),
  STD_HRS_WK      NUMBER,
  GRADE           VARCHAR2(30 BYTE),
  GRADE_DESCR     VARCHAR2(30 BYTE),
  DATA_DATE       DATE
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY DATA_DIR
     ACCESS PARAMETERS
       ( records delimited by newline
  NOLOGFILE
  BADFILE DATA_DIR:'EXTHR.bad'
  skip 1
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
   )
     LOCATION (DATA_DIR:'EXTHR.csv')
  )
REJECT LIMIT 0
LOGGING
NOCACHE
NOPARALLEL;

GL   AND   AP
GL_CODE_COMBINATIONS              AP_INVOICES_ALL
code_combination_id                 =             acct_pay_code_combination_id
GL_CODE_COMBINATIONS              AP_INVOICES_DISTRIBUTIONS_ALL
code_combination_id                 =             dist_code_combination_id
GL_SETS_OF_BOOKS                      AP_INVOICES_ALL
set_of_books_id                        =         set_of_books_id

GL   AND AR
GL_CODE_COMBINATIONS               RA_CUST_TRX_LINE_GL_DIST_ALL
code_combination_id                 =          code_combination_id

AP
ap_invoice_payments_all                      aipa
,ap_invoices_all                                   aia
,ap_lookup_codes                                alc
,ap_invoice_distributions_all                  aida
,gl_code_combinations_v                      gcc
WHERE  aipa.invoice_id                           = aia.invoice_id
AND    alc.lookup_type                              LIKE 'INVOICE TYPE'
AND    aia.invoice_type_lookup_code         = alc.lookup_code
AND    aia.invoice_id                                 = aida.invoice_id
AND    aida.distribution_line_number          = 1
AND    aida.dist_code_combination_id        = gcc.code_combination_id

ap_inv_selection_criteria_all  aisca
,ap_checks_all                     aca
,po_vendors                         pv
,ap_bank_accounts_all         abaa
,ap_check_stocks_all           acsa
,po_vendor_sites         pvsa

where     aisca.checkrun_id              = aca.checkrun_id
AND      pv.vendor_id                   = aca.vendor_id
AND      aca.bank_account_id            = abaa.bank_account_id
AND      aca.check_stock_id             = acsa.check_stock_id
AND      aca.payment_method_lookup_code LIKE 'CHECK'
AND      aca.status_lookup_code         != 'VOIDED'
AND     aca.vendor_id         =  pvsa.vendor_id
AND     aca.vendor_site_id           =  pvsa.vendor_site_id

GL   AND INV
GL_CODE_COMBINATIONS                 MTL_SYSTEM_ITEMS_B
code_combination_id                 =               cost_of_sales_account


GL   AND PO
GL_CODE_COMBINATIONS                  PO_DISTRIBUTIONS_ALL
code_combination_id                 =           code_combination_id


PO AND AP
PO_DISTRIBUTIONS_ALL                       AP_INVOICE_DISTRIBUTIONS_ALL
Po_distribution_id                      =                    po_distribution_id

PO_VENDORS                                       AP_INVOICES_ALL
vendor_id                                   =                    vendor_id

PO AND SHIPMENTS
PO_HEADERS_ALL                                  RCV_TRANSACTIONS
Po_header_id                            =                             po_header_id

PO_DISTRIBUTIONS_ALL                          RCV_TRANSACTIONS
Po_distribution_id                      =                  po_distribution_id

SHIPMENTS AND INVOICE
RCV_TRANSACTIONS                               AP_INVOICE_DISTRIBUTIONS_ALL
RCV_TRANSACTION_ID            =                       RCV_TRANSACTION_ID


PO AND  INV
PO_REQUISITION_LINES_ALL                  MTL_SYSTEM_ITEMS_B
item_id                                          =           inventory_item_id
org_id                                            =           organization_id

PO AND HRMS
PO_HEADERS_ALL                                  HR_EMPLOYEES
Agent_id                                        =             employee_id

PO AND REQUISITION
PO_DISTRIBUTIONS_ALL                          PO_REQ_DISTRIBUTIONS_ALL
req_distribution_id                          =             distribution_id


SHIPMENTS AND INV
RCV_TRANSACTIONS                                MTL_SYSTEM_ITEMS_B
Organization_id                              =             organization_id

INV AND HRMS
MTL_SYSTEM_ITEMS_B                             HR_EMPLOYEES
buyer_id                                        =             employee_id

OM  AND  AR
OE_ORDER_HEADERS_ALL                       RA_CUSTOMER_TRX_LINES_ALL
TO_CHAR( Order_number)              =   interface_line_attribute1
OE_ORDER_LINES_ALL                              RA_CUSTOMER_TRX_LINES_ALL
TO_CHAR(Line_id)                         =   interface_line_attribute6


OE_ORDER_LINES_ALL                          RA_CUSTOMER_TRX_LINES_ALL
reference_customer_trx_line_id        =   customer_trx_line_id

OM AND SHIPPING
OE_ORDER_HEADERS_ALL                 WSH_DELIVARY_DETAILS
HEADER_ID                                  =        SOURCE_HEADER_ID

OE_ORDER_HEADERS_ALL                    WSH_DELIVARY_DETAILS
LINE_ID                                         =          SOURCE_LINE_ID

WSH_DELIVERY_DETAILS.LINE_ID  =  MTL_TXN_REQUEST_LINES.MOVE_ORDER_LINE_ID

AP AND AR (BANKS)
AR_CASH_RECEIPTS_ALL                         AP_BANK_ACCOUNTS
REMITTANCE_BANK_ACCOUNT_ID      =        ABA.BANK_ACCOUNT_ID

AP AND AR
HZ_PARTIES                                                AP_INVOICES_ALL
PARTY_ID                                            =           PARTY_ID

OM AND CRM
OE_ORDER_LINES_ALL                 CSI_ITEM_INSTANCES(Install Base)
LINE_ID                                  =          LAST_OE_ORDER_LINE_ID

Table Name: Po_Requisition_Headers_All A
Column Names             Table Name             Column Name
A. REQUISITION_HEADER_ID      PO_REQUISITION_LINES_ALL      REQUISITION_HEADER_ID
A. TYPE_LOOKUP_CODE           PO_DOCUMENT_TYPES         DOCUMENT_SUBTYPE
A. PREPARER_ID                       PER_PEOPLE_F             PERSON_ID
A. ORG_ID                                  MTL_SYSTEM_ITEMS                  ORGANIZATION_ID
A. ORG_ID                                  MTL_ORGANIZATIONS                 ORGANIZATION_ID

Table Name: Po_Requisition_Lines_All B

Column Names                   Table Name             Column Name
B .REQUISITION_HEADER_ID     PO_REQUISITION_HEADERS_ALL             REQUISITION_HEADER_ID
B .REQUISITION_LINE_ID         PO_REQ_DISTRIBUTIONS_ALL                 REQUISITION_LINE_ID
B .LINE_TYPE_ID                 PO_LINE_TYPES                         LINE_TYPE_ID
B .ITEM_ID                 MTL_SYSTEM_ITEMS                INVENTORY_ITEM_ID
B .ORG_ID                 MTL_SYSTEM_ITEMS                ORGANIZATION_ID

Table Name: Po_Requisition_Distributions_All C .
Column Names                    Table Name                  Column Name
C .REQUISITION_LINE_ID         PO_REQUISITION_LINES_ALL     REQUISITION_LINE_ID
C .DISTRIBUTION_ID          PO_DISTRIBUTIONS_ALL         REQ_DISTRIBUTION_ID
C .SET_OF_BOOKS_ID          GL_SETS_OF_BOOKS         SET_OF_BOOKS_ID
C .CODE_COMBINATION_ID      GL_CODE-COMBINATIONS              CODE_COMBINATION_ID

Table Name: Po_Distributions_All D .
Column Names                 Table Name              Column Name
D .PO_LINE_ID             PO_LINES             PO_LINE_ID
D .REQ_DISTRIBUTION_ID         PO_REQ_DISTRIBUTIONS_ALL     DISTRIBUTION_ID
D .PO_DISTRIBUTION_ID         AP_INVOICE_DISTRIBUTIONS_ALL     PO_DISTRIBUTION_ID

Table Name: Po_Headers_All E .
Column Names                 Table Name                Column Name
E .PO_HEADER_ID         PO_LINES             PO_HEADER_ID
E .PO_HEADER_ID                           RCV_SHIPMENT_LINES         PO_HEADER_ID
E .VENDOR_ID             PO_VENDORS             VENDOR_ID
E .AGENT_ID             PER_PEOPLE             PERSON_ID
E .TYPE_LOOK_UP_CODE         PO_DOCUMENT_TYPES         DOCUMENT_SUBTYPE

Table Name: Po_Lines_All F.
Column Names             Table Name             Column Name
F.PO_HEADER_ID     PO_HEADERS             PO_HEADER_ID
F.PO_LINE_ID         PO_DISTRIBUTIONS_ALL         PO_LINE_ID
F.ITEM_ID         MTL_SYSTEM_ITEMS         ITEM_ID

Table Name: Rcv_Shipment_Lines G.
Column Names                 Table Name             Column Name
G.PO_HEADER_ID         PO_HEADERS_ALL             PO_HEADER_ID
G.SHIPMENT_HEADER_ID                RCV_SHIPMENT_HEADERS             SHIPMENT_HEADER_ID

Table Name: Rcv_Shipment_Headers G.
Column Names                 Table Name             Column Name
G.VENDOR_ID                          PO_VENDORS              VENDOR_ID
SHIPMENT_HEADER_ID                      RCV_TRANSACTIONS                     SHIPMENT_HEADER_ID

Table Name : Rcv_Transactions
Column Names                 Table Name             Column Name
TRANSACTION_ID                                 GL_JE_LINES                              TO_NUMBER(REFERENCE_5)

Table Name: Ap_Invoices_All H.
Column Names             Table Name                    Column Name
H. INVOICE_ID         AP_INVOICE_DISTRIBUTIONS_ALL             INVOICE_ID

Table Name: Oe_Order_Headers_All I.
Column Names                              Table Name                                Column Name
I.HEADER_ID                             OE_ORDER_LINES                     HEADER_ID
I.SOURCE_HEADER_ID              WISH_DELIVERY_DETAILS         SOURCE_HEADER_ID
I.PRICE_LIST_ID                         QP_LIST_HEADERS_TL              LIST_HEADER_ID
I.ORG_ID                                    MTL_ORGANIZATIONS               ORGANIZATION_ID
I.SALESREP_ID                          JTF_RS_SALESREPS                 SALESREP_ID
I.ORDER_TYPE_ID                     OE_TRANSACTION_TYPES        TRANSACTION_TYPE_ID
I.ORDER_SOURCE_ID                OE_ORDER_SOURCES             ORDER_SOURCE_ID
I.ACCOUNTING_RULE_ID            RA_RULES                                RULE_ID
I.PAYMENT_TERM_ID                 AP_TERMS                               TERM_ID
I.SOLD_TO_ORG_ID                   HZ_CUST_ACCOUNTS                CUST_ACCOUNT_ID
I.SHIP_FROM_ORG_ID               MTL_PARAMETERS                   ORGANIZATION_ID
I.SHIP_TO_ORG_ID                    HZ_CUST_SITE_USES_ALL        SITE_USE_ID
I.INVOICE_TO_ORG_ID               HZ_CUST_SITE_USES_ALL        SITE_USE_ID
I.SALESREP_ID                         RA_SALESREPS_ALL                SALESREP_ID
I.ORDER_NUMBER                    RA_CUSTOMER_TRX_ALL          CT_REFERENCE
I.ORG_ID                   HR_OPERATING_UNITS    ORGANIZATION_ID

Table Name: Oe_Order_Lines_All J.
Column Names                                         Table Name                                   Column Name
J.LINE_TYPE_ID                                OE_TRANSACTION_TYPES_TL       TRANSACTION_TYPE_ID
J.INVOICING_RULE_ID                       RA_RULES                                     RULE_ID
J.ORG_ID            HR_OPERATING_UNITS        ORGANIZATION_ID
J.LINE_ID            MTL_MATERIAL_TRANSACTIONS    TRX_SOURCE_LINE_ID

Table Name: Hz_Parties K.
Column Names                 Table Name             Column Name
K.PATY_ID             HZ_CUST_ACCOUNTS         PARTY_ID
K.CUST_ACCOUNT_ID                          OE_ORDER_LINES         SOLD_TO_ORG_ID

Table Name: Hz_Party_Sites_All L.
Column Names                 Table Name             Column Name
L.PATY_ID             HZ_PARTIES             PATY_ID
L. LOCATION_ID             HZ_LOCATIONS            LOCATION_ID

HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID      =   HZ_CUST_SITE_USES_ALL.CUST_ACCT_SITE_ID
HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_ID               =   hz_cust_accounts.CUST_ACCT_ID


Table Name: Wsh_delivery_details M.
Column Names                 Table Name             Column Name
M.SOURCE_HEADER_ID         OE_ORDER_HEADERS         SOURCE_HEADER_ID
M.DELIVERY_DETAIL_ID         WSH_DELIVERY_ASSIGNMENTS     DELIVERY_DETAIL_ID
M.DELIVERY_ID             WSH_NEW_DELIVERIES         DELIVERY_ID
M.INVENTORY_ITEM_ID         MTL_SYSTEM_ITEMS         INVENTORY_ITEM_ID

Table Name: RA_CUSTOMER_TRX_ALL N.
Column Names                 Table Name                 Column Name
N.CUSTOMER_TRX_ID         AR_RECEIVABLE_APPLICATIONS_ALL     APPLIED_CUSTOMER_TRX_ID
N.TERM_ID             RA_TERMS                 TERM_ID
N.CUSTOMER_TRX_ID         RA_CUST_TRX_LINE_GL_DIST         CUSTOMER_TRX_ID
N.SOLD_TO_CUSTOMER_ID             RA_CUSTOMERS                                            CUSTOMER_ID
N.CUSTOMER_TRX_ID        RA_CUSTOMER_TRX_LINES_ALL                    CUSTOMER_TRX_ID
N.BILL_TO_CUSTOMER_ID        HZ_CUST_ACCT_SITES_ALL          CUST_ACCOUNT_ID
N.BILL_TO_SITE_USE_ID        HZ_CUST_ACCT_SITES_ALL                           SITE_USE_ID
N.CUST_TRX_TYPE_ID        RA_CUST_TRX_TYPES_ALL                            CUST_TRX_TYPE_ID

RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID       =      RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_LINE_ID
TO_NUMBER(RA_CUSTOMER_TRX_LINE_ALL.INTERFACE_LINE_ATTRIBUTE6)  =     JA_IN_SO_PICKING_LINES.DELIVERY_ID=WND.NAME=WND.DELIVERY_ID               

Table Name: AR_CASH_RECEIPTS_ALL O.
Column Names                     Table Name                 Column Name
O.CASH_RECEIPT_ID                      AR_RECEIVABLE_APPLICATIONS_ALL     CASH_RECEIPT_ID
O.SET_OF_BOOKS_ID                      GL_SETS_OF_BOOKS             SET_OF_BOOKS_ID
O.RECEIPT_METHOD_ID                               AR_RECEIPT_METHODS            RECEIPT_METHOD_ID
O.CASH_RECEIPT_ID                                    AR_CASH_RECEIPT_HISTORY_ALL        CASH_RECEIPT_ID
O.PAY_FROM_CUSTOMER                     HZ_CUST_ACCOUNTS            CUST_ACCOUNT_ID
O.REMITTANCE_BANK_ACCOUNT_ID     AP_BANK_ACCOUNTS            BANK_ACCOUNT_ID

AR_RECEIVABLE_APPLICATIONS_ALL.PAYMENT_SCHEDULE_ID=AR_PAYMENT_SCHEDULE_ALL.PAYMENT_SCHEDULE_ID

 AP_BANK_ACCOUNTS.BANK_ACCOUNT_ID=AP_BANK_BRANCHES.BANK_BRANCH_ID

FND_LOOKUP_VALUES.lookup_code        =   HZ_LOCATIONS.country
  hz_cust_site_uses_all.cust_acct_site_id= hz_cust_acct_sites_all.cust_acct_site_id(+) AND
  hz_cust_acct_sites_all.party_site_id = hz_party_sites.party_site_id(+) AND
  hz_locations.location_id(+) = hz_party_sites.location_id AND
FOR BILL TO:   hz_cust_site_uses_all.SITE_USE_ID     = OOHA.invoice_to_org_id

FOR SHIP TO:   hz_cust_site_uses_all.SITE_USE_ID    = OOHA.ship_to_org_id

PA:

apps.pa_projects_all ppa,
apps.pa_tasks ppt,
apps.hr_all_organization_units hou,
apps.pa_project_classes ppc,
apps.pa_cost_distribution_lines_all pcd,
apps.pa_expenditures_all e,
apps.pa_expenditure_items_all pei,
apps.pa_expenditure_types pet,
fnd_flex_value_children_v a,
fnd_id_flex_segments j,
fnd_flex_values_vl xx
WHERE ppa.carrying_out_organization_id = hou.organization_id
AND ppa.project_id = ppt.project_id
AND ppa.project_id = pei.project_id
AND pei.task_id = ppt.task_id
AND e.expenditure_id = pei.expenditure_id
AND ppa.project_id = ppc.project_id
AND pei.expenditure_item_id = pcd.expenditure_item_id
AND pet.expenditure_type = pei.expenditure_type
AND a.flex_value_set_id = j.flex_value_set_id
AND UPPER (j.segment_name) = 'ACCOUNT'
AND j.id_flex_code = 'GLLE'
AND ppt.attribute1 = a.flex_value

SELECT DISTINCT
 FCPT.USER_CONCURRENT_PROGRAM_NAME
,FCPT.CONCURRENT_PROGRAM_NAME CP_SHORT_NAME
,FCPT.DESCRIPTION CP_DESC
,FCPT.APPLICATION_ID
--,FCP.CONCURRENT_PROGRAM_NAME SHORT_NAME
,FCP.OUTPUT_FILE_TYPE
,DECODE(FCP.ENABLED_FLAG,'Y','ACTIVE','N','INACTIVE')STATUS
,FEX.EXECUTABLE_NAME
,FEX.APPLICATION_NAME
,FEX.DESCRIPTION EX_DESC
,DECODE(FEX.EXECUTION_METHOD_CODE,'I','PL/SQL Stored Procedure','H','Host','P','Oracle Reports',
                                 'K','Java Concurrernt Program','Q','SQL*Plus','M','Multi Lanuage Function',
                                 'A','Spawned','J','Java Stored Procedure','B','Request Set Stage Function',
                                 'S','Immediate','L','SQL*Loader','P','Perl Concurrent Program')EXECUTION_METHOD
,FEX.EXECUTION_FILE_NAME
,FDP.END_USER_COLUMN_NAME PARAMETERS
,FDP.SRW_PARAM TOKEN
,FORM_LEFT_PROMPT PROMPT
,FFVS.FLEX_VALUE_SET_NAME
--,FRU.REQUEST_GROUP_ID
--,FRU.REQUEST_GROUP_NAME
,FAT.APPLICATION_NAME Module_name
,FAT.APPLICATION_ID
FROM
--FND_CONCURRENT_PROGRAMS_TL FCPT,
FND_CONCURRENT_PROGRAMS_VL FCPT
,FND_CONCURRENT_PROGRAMS FCP
,FND_EXECUTABLES_FORM_V FEX
,FND_DESCR_FLEX_COL_USAGE_VL FDP
,FND_FLEX_VALUE_SETS FFVS
--,FND_CONCURRENT_REQUESTS FCR
--,FND_RESPONSIBILITY_VL FRT
,FND_REQUEST_GROUP_UNITS FRU
,FND_APPLICATION_TL FAT
WHERE
FCPT.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCP.ENABLED_FLAG='Y'
AND FCPT.EXECUTABLE_ID = FEX.EXECUTABLE_ID
AND FCPT.EXECUTABLE_APPLICATION_ID=FEX.APPLICATION_ID
AND FCPT.APPLICATION_ID = FDP.APPLICATION_ID
AND FCPT.CONCURRENT_PROGRAM_NAME = SUBSTR(FDP.DESCRIPTIVE_FLEXFIELD_NAME,7)   
AND FDP.FLEX_VALUE_SET_ID=FFVS.FLEX_VALUE_SET_ID
AND FCPT.CONCURRENT_PROGRAM_ID = FRU.REQUEST_UNIT_ID(+)
--AND FRU.UNIT_APPLICATION_ID = FAT.APPLICATION_ID
AND FRU.APPLICATION_ID = FAT.APPLICATION_ID(+)
--AND FEX.APPLICATION_NAME like '%Busi%'
AND FCPT.USER_CONCURRENT_PROGRAM_NAME like 'Suppliers Report'
ORDER BY FCPT.USER_CONCURRENT_PROGRAM_NAME


--------------------140620130633

TO KNOW THE HOW MANY USERS HAVING THAT PARTICULAR RESPONSIBILITY
------------------------------------------------------------------------------------------------
SELECT FR.RESPONSIBILITY_NAME,
 FU.USER_NAME,
 FUR.START_DATE,
 FUR.END_DATE
 FROM
 FND_USER_RESP_GROUPS_DIRECT FUR,
 APPS.FND_RESPONSIBILITY_TL FR
 ,FND_USER FU
 WHERE FUR.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
 AND FR.RESPONSIBILITY_NAME LIKE '%AP%'
 AND FUR.USER_ID = FU.USER_ID
 AND FUR.END_DATE IS NULL