Package
----------------
Create table xxxx_po_headers
(
 po_id INTEGER
,vendor_id INTEGER
,suggested_vendor_id INTEGER
,po_description VARCHAR2(250)
,po_status    VARCHAR2(30)
,comments_from_approver VARCHAR2(2000)
,send_email_to VARCHAR2(300)
) ;







CREATE OR REPLACE PACKAGE xxxx_po_wf_training_pkg IS
  PROCEDURE is_po_valid
  (
    itemtype IN VARCHAR2
   ,itemkey  IN VARCHAR2
   ,actid    IN NUMBER
   ,funcmode IN VARCHAR2
   ,RESULT   IN OUT VARCHAR2
  );

  PROCEDURE set_wf_approver_role
  (
    itemtype IN VARCHAR2
   ,itemkey  IN VARCHAR2
   ,actid    IN NUMBER
   ,funcmode IN VARCHAR2
   ,RESULT   IN OUT VARCHAR2
  );

  PROCEDURE set_wf_status_to_validated
  (
    itemtype IN VARCHAR2
   ,itemkey  IN VARCHAR2
   ,actid    IN NUMBER
   ,funcmode IN VARCHAR2
   ,RESULT   IN OUT VARCHAR2
  );

  PROCEDURE start_training_wf(p_po_id IN INTEGER);

END xxxx_po_wf_training_pkg;
/







CREATE OR REPLACE PACKAGE BODY xxxx_po_wf_training_pkg IS

  FUNCTION is_po_validated(p_po_id IN INTEGER) RETURN BOOLEAN IS
  BEGIN
    IF p_po_id > 0
    THEN
      RETURN TRUE;
    END IF;
    RETURN FALSE;
  END is_po_validated;

  PROCEDURE is_po_valid
  (
    itemtype IN VARCHAR2
   ,itemkey  IN VARCHAR2
   ,actid    IN NUMBER
   ,funcmode IN VARCHAR2
   ,RESULT   IN OUT VARCHAR2
  ) IS
  BEGIN
    IF (funcmode != 'RUN')
    THEN
      RETURN;
    END IF;
    --Just a pseudo validation
    --call a function here, and either assign COMPLETE:Y or COMPLETE:N

    IF is_po_validated(p_po_id => wf_engine.getitemattrnumber(itemtype => itemtype
                                                             ,itemkey  => itemkey
                                                             ,aname    => 'PO_ID'))
    THEN
      RESULT := 'COMPLETE:Y';
    ELSE
      RESULT := 'COMPLETE:N';
    END IF;
  END is_po_valid;

  PROCEDURE set_wf_approver_role
  (
    itemtype IN VARCHAR2
   ,itemkey  IN VARCHAR2
   ,actid    IN NUMBER
   ,funcmode IN VARCHAR2
   ,RESULT   IN OUT VARCHAR2
  ) IS
    v_role_email xxxx_po_headers.send_email_to%TYPE;
    n_ctr        INTEGER := 0;
  BEGIN
    v_role_email := upper(wf_engine.getitemattrtext(itemtype => itemtype
                                                   ,itemkey  => itemkey
                                                   ,aname    => 'SEND_TO_EMAIL'));
    SELECT COUNT(*)
    INTO   n_ctr
    FROM   wf_local_roles
    WHERE  NAME = v_role_email;

    IF n_ctr = 0
    THEN
      wf_directory.createadhocrole(role_name               => v_role_email
                                  ,role_display_name       => v_role_email
                                  ,role_description        => v_role_email
                                  ,notification_preference => 'MAILHTML'
                                  ,email_address           => v_role_email
                                  ,status                  => 'ACTIVE'
                                  ,expiration_date         => NULL);
    END IF;
    wf_engine.setitemattrtext(itemtype => itemtype
                             ,itemkey  => itemkey
                             ,aname    => 'SEND_TO_ROLE'
                             ,avalue   => v_role_email);
    RESULT := 'COMPLETE:Y';

    RESULT := 'COMPLETE:Y';
  END set_wf_approver_role;

  PROCEDURE set_wf_status_to_validated
  (
    itemtype IN VARCHAR2
   ,itemkey  IN VARCHAR2
   ,actid    IN NUMBER
   ,funcmode IN VARCHAR2
   ,RESULT   IN OUT VARCHAR2
  ) IS
  BEGIN
    UPDATE xxxx_po_headers
    SET    po_status = 'VALIDATED'
    WHERE  po_id = wf_engine.getitemattrnumber(itemtype, itemkey, 'PO_ID');
    RESULT := 'COMPLETE:Y';  
  END set_wf_status_to_validated;

  PROCEDURE start_training_wf(p_po_id IN INTEGER) IS
    l_itemtype VARCHAR2(30) := 'XXXXPTR';
    l_itemkey  VARCHAR2(300) := 'TRAINING-' || p_po_id;
    CURSOR c_get IS
      SELECT *
      FROM   xxxx_po_headers
      WHERE  po_id = p_po_id;
    p_get c_get%ROWTYPE;
  BEGIN
    OPEN c_get;
    FETCH c_get
      INTO p_get;
    CLOSE c_get;

    wf_engine.createprocess(l_itemtype, l_itemkey, 'MAIN_TRAINING_PROCESS');

    wf_engine.setitemuserkey(itemtype => l_itemtype
                            ,itemkey  => l_itemkey
                            ,userkey  => 'USERKEY: ' || l_itemkey);
    wf_engine.setitemowner(itemtype => l_itemtype
                          ,itemkey  => l_itemkey
                          ,owner    => 'SYSADMIN');

    wf_engine.setitemattrnumber(itemtype => l_itemtype
                               ,itemkey  => l_itemkey
                               ,aname    => 'PO_ID'
                               ,avalue   => p_po_id);

    wf_engine.setitemattrtext(itemtype => l_itemtype
                             ,itemkey  => l_itemkey
                             ,aname    => 'SEND_TO_EMAIL'
                             ,avalue   => p_get.send_email_to);
    wf_engine.setitemattrtext(itemtype => l_itemtype
                             ,itemkey  => l_itemkey
                             ,aname    => 'PO_DESCRIPTION'
                             ,avalue   => p_get.po_description);
    wf_engine.startprocess(l_itemtype, l_itemkey);

  END start_training_wf;

END xxxx_po_wf_training_pkg;
/







INSERT INTO xxxx_po_headers
  (po_id
  ,vendor_id
  ,suggested_vendor_id
  ,po_description
  ,po_status
  ,comments_from_approver
  ,send_email_to)
VALUES
  (1000 --po_id
  ,10000 --vendor_id
  ,NULL --suggested_vendor_id
  ,'This is PO Training Description' --po_description
  ,'INITIAL' --po_status
  ,NULL --comments_from_approver
  ,'ANILPASSI@GMAIL.com' --send_email_to
   );

declare
begin
  commit ;

end ;
/

 

declare
begin
  xxxx_po_wf_training_pkg.start_training_wf(p_po_id => 1000);
end ;
/

Triiger

CREATE OR REPLACE TRIGGER XX.TT_OE_CUSTOM_ALERT
AFTER INSERT OR UPDATE ON APPS.OE_ORDER_HEADERS_ALL
FOR EACH ROW
WHEN (NEW.ATTRIBUTE16 IS NOT NULL AND NEW.FLOW_STATUS_CODE = 'BOOKED')
DECLARE

V TT_CUSTOM_ALERTS%ROWTYPE;



BEGIN



SELECT *

INTO   V

FROM  TT_CUSTOM_ALERTS

WHERE  ORDER_NUMBER = :NEW.ORDER_NUMBER

AND      ORDER_TYPE_ID = :NEW.ORDER_TYPE_ID;



IF :NEW.ATTRIBUTE16 != :OLD.ATTRIBUTE16 THEN



UPDATE TT_CUSTOM_ALERTS

SET      NO_TIMES_UDT = 3

WHERE  ORDER_NUMBER = :NEW.ORDER_NUMBER

AND      ORDER_TYPE_ID = :NEW.ORDER_TYPE_ID;



END IF;



EXCEPTION WHEN NO_DATA_FOUND THEN

INSERT INTO ITW_CUSTOM_ALERTS (ORDER_NUMBER,LIKELY_DISPATCH_DATE,NO_TIMES,ORDER_TYPE_ID,NO_TIMES_UDT)
                              VALUES(:NEW.ORDER_NUMBER,
                                              :NEW.ATTRIBUTE16,
                                                  1,
                                                  :NEW.ORDER_TYPE_ID,1);
END;

TRIGGER

CREATE OR REPLACE TRIGGER APPS.INV_MISC_ISSUE_BOM_COMPS
AFTER INSERT ON APPS.MTL_MATERIAL_TRANSACTIONS
FOR EACH ROW
DECLARE
ln_request_id NUMBER;
lv_count      NUMBER;
ln_user_id NUMBER;
ln_responsibility_id NUMBER;
ln_responsibility_app_id NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
ln_user_id:=fnd_profile.value('USER_ID');
ln_responsibility_id:=fnd_profile.value('RESP_ID');
ln_responsibility_app_id :=fnd_profile.value('RESP_APPL_ID');
fnd_global.apps_initialize(ln_user_id,ln_responsibility_id,ln_responsibility_app_id);
--IF :new.transaction_type_id = 262 OR :new.transaction_type_id=42 THEN ----modified on 08-feb-2013 according to harish
IF (:new.transaction_type_id = 262 OR :new.transaction_type_id=42) and (:new.reason_id=4) THEN
--  raise_application_error(-20010,'Misc Receipt');
--262  -Miscellaneous Receipt(RG Update)
--42    --Miscellaneous Receipt
   BEGIN
     select count(jil.item_id)
     into   lv_count
     from   apps.ja_in_osp_items_hdr jih
     ,     apps.ja_in_osp_items_dtl jil
     WHERE  1=1
     AND    jih.osp_item_id     =    jil.osp_item_id
     AND    jih.organization_id=:new.organization_id
     AND    jih.osp_item_id=:new.inventory_item_id;
   END;
IF lv_count > 0 THEN
  --raise_application_error(-20010,'No Component is Defined for this FG Item');
ln_request_id:=  fnd_request.Submit_request(  'INV'
                                             ,'STX_BOM_MISC_ISSUE'
                                             ,''  -- NEW CHANGE'
                                             ,''
                                             ,FALSE
                                             ,:new.organization_id
                                             ,:new.inventory_item_id
                                             ,:new.transaction_date
                                             ,:new.transaction_quantity
                                             ,:new.subinventory_code
                                             ,:new.transaction_type_id
                                             ,:new.distribution_account_id
                                             ,:new.created_by
                                             ,:new.transaction_reference
                                             ,chr(0),chr(0),CHR(0),chr(0),chr(0),CHR(0)
                                             ,chr(0),chr(0),CHR(0),chr(0),chr(0),chr(0),CHR(0)
                                             ,chr(0),chr(0),CHR(0),chr(0),chr(0),CHR(0),chr(0)
                                             ,chr(0),chr(0),CHR(0),chr(0),chr(0),CHR(0),chr(0)
                                             ,chr(0),CHR(0),chr(0),chr(0),chr(0),CHR(0),chr(0)
                                             ,chr(0),CHR(0),chr(0),chr(0),CHR(0),chr(0),chr(0)
                                             ,chr(0),CHR(0),chr(0),chr(0),CHR(0),chr(0),chr(0)
                                             ,CHR(0),chr(0),chr(0),chr(0),CHR(0),chr(0),chr(0)
                                             ,CHR(0),chr(0),chr(0),CHR(0),chr(0),chr(0),chr(0)
                                             ,CHR(0),chr(0),chr(0),CHR(0),chr(0),chr(0),CHR(0)
                                             ,chr(0),chr(0),chr(0),chr(0),chr(0),chr(0),CHR(0)
                                             ,chr(0),chr(0),chr(0),chr(0),chr(0),chr(0),CHR(0)
                                            ,chr(0),chr(0),chr(0),chr(0),chr(0),chr(0),CHR(0),CHR(0));


 END IF; --  Checking the count

 --commit;

    END IF; --Checking the transaction_type_id
COMMIT;
END;

TO GET THE CONTACT PERSONS IN AR(CUSTOMER STANDARD)
--------------------------------

SELECT DISTINCT
--CUST.cust_account_id CUSTOMER_ID,
CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER,
PARTY.PARTY_NAME,
loc.ADDRESS1, loc.ADDRESS2, loc.ADDRESS3, loc.ADDRESS4, loc.CITY,
loc.POSTAL_CODE, loc.STATE,loc.COUNTY, --HCP.EMAIL_ADDRESS,
RAA.SITE_NUMBER, --RAA.ADDRESS_ID,ship.CUST_ACCT_SITE_ID,
RAC.LAST_NAME,
RAC.EMAIL_ADDRESS
FROM hz_cust_accounts cust,
APPS.hz_cust_acct_sites_all acct,
APPS.hz_cust_site_uses_all ship,
APPS.hz_party_sites party_site,
APPS.hz_locations loc,
APPS.hz_parties party,
APPS.HZ_CONTACT_POINTS HCP,
APPS.RA_ADDRESSES_ALL RAA,
APPS.RA_CONTACTS RAC
WHERE cust.cust_account_id = acct.cust_account_id
AND acct.cust_acct_site_id = ship.cust_acct_site_id
AND acct.org_id = ship.org_id
AND cust.status = 'A'
and ship.SITE_USE_CODE ='BILL_TO'
AND loc.location_id = party_site.location_id
AND acct.party_site_id = party_site.party_site_id
AND cust.party_id = party.party_id
--AND RAC.CUSTOMER_ID = cust.CUST_ACCOUNT_ID
AND RAC.ADDRESS_ID = RAA.ADDRESS_ID
AND HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
 AND HCP.PRIMARY_FLAG(+) = 'Y'
 AND HCP.CONTACT_POINT_TYPE(+) = 'EMAIL'
 AND HCP.OWNER_TABLE_ID(+) = PARTY.PARTY_ID
 AND RAA.PARTY_ID = PARTY.PARTY_ID
 AND ship.CUST_ACCT_SITE_ID = RAA.ADDRESS_ID
--AND CUST.ACCOUNT_NUMBER = 'SN000014'


SELECT * FROM APPS.RA_CONTACTS

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