SQL Query to find Security profiles attached to an User

SELECT
    fa.application_short_name,
    fpo.profile_option_name,
    fpo.user_profile_option_name,
    fu.user_name,
    fpov.profile_option_value
FROM
    fnd_profile_options_vl      fpo,
    fnd_application             fa,
    fnd_profile_option_values   fpov,
    fnd_user                    fu
WHERE
    fpo.application_id = fa.application_id
    AND fpo.profile_option_id = fpov.profile_option_id
    AND fpo.application_id = fpov.application_id
    AND fpov.level_value = fu.user_id
    AND fu.user_name = '&1'

Standard PO's for last 12 months

SELECT
    pha.segment1                po_no,
    trunc(pha.creation_date) po_date,
    pha.currency_code           po_currency,
    nvl(pha.rate, 1) po_rate,
    nvl2(pla.item_id, msib.segment1, NULL) item,
    nvl2(pla.item_id, msib.description, pla.item_description) item_desc,
    msib.primary_uom_code       item_uom,
    mck.concatenated_segments   item_category,
    pll.price_override          item_po_price,
    aps.vendor_name             supplier_name,
    nvl((nvl(pll.quantity, 0) - nvl(pll.quantity_cancelled, 0)), 0) ordered_qty,
    ( nvl((nvl(pll.quantity, 0) - nvl(pll.quantity_cancelled, 0)), 0) * pll.price_override ) ordered_value_po_curr,
    (
        CASE pha.currency_code
            WHEN 'AED' THEN
                ( nvl((nvl(pll.quantity, 0) - nvl(pll.quantity_cancelled, 0)), 0) * pll.price_override )
            ELSE
                ( ( nvl((nvl(pll.quantity, 0) - nvl(pll.quantity_cancelled, 0)), 0) * pll.price_override ) * nvl(pha.rate, 1) )
        END
    ) ordered_value_aed
FROM
    apps.po_headers_all              pha,
    apps.po_lines_all                pla,
    apps.po_distributions_all        pda,
    apps.gl_code_combinations        gcc,
    apps.po_line_locations_all       pll,
    apps.hr_all_organization_units   hao,
    apps.mtl_system_items_b          msib,
    apps.mtl_categories_kfv          mck,
    apps.po_agents_v                 pov,
    apps.ap_suppliers                aps,
    apps.ap_supplier_sites_all       ass
WHERE
    pha.po_header_id = pla.po_header_id
    AND pla.po_line_id = pll.po_line_id
    AND pll.ship_to_organization_id = hao.organization_id
    AND pll.po_release_id IS NULL
    AND pla.item_id = msib.inventory_item_id (+)
    AND pha.po_header_id = pda.po_header_id
    AND pla.po_line_id = pda.po_line_id
    AND pda.code_combination_id = gcc.code_combination_id
    AND msib.organization_id (+) = 63
    AND pla.category_id = mck.category_id
    AND pha.agent_id = pov.agent_id
    AND pha.vendor_id = aps.vendor_id
    AND pha.vendor_site_id = ass.vendor_site_id
    AND aps.vendor_id = ass.vendor_id
    AND pha.type_lookup_code = 'STANDARD'
    AND trunc(pha.creation_date) BETWEEN trunc(add_months(sysdate, - 12), 'MONTH') AND trunc(sysdate, 'MONTH')
ORDER BY
    trunc(pha.creation_date) ASC

FND_PROFILE & FND_GLOBAL values

Following are the FND_PROFILE values that can be used in the PL/SQL code:

   fnd_profile.value('PROFILEOPTION');
   fnd_profile.value('MFG_ORGANIZATION_ID');
   fnd_profile.value('ORG_ID');
   fnd_profile.value('LOGIN_ID');
   fnd_profile.value('USER_ID');
   fnd_profile.value('USERNAME');
   fnd_profile.value('CONCURRENT_REQUEST_ID');
   fnd_profile.value('GL_SET_OF_BKS_ID');
   fnd_profile.value('SO_ORGANIZATION_ID');
   fnd_profile.value('APPL_SHRT_NAME');
   fnd_profile.value('RESP_NAME');
   fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:

   FND_GLOBAL.USER_ID;
   FND_GLOBAL.APPS_INTIALIZE;
   FND_GLOBAL.LOGIN_ID;
   FND_GLOBAL.CONC_LOGIN_ID;
   FND_GLOBAL.PROG_APPL_ID;
   FND_GLOBAL.CONC_PROGRAM_ID;
   FND_GLOBAL.CONC_REQUEST_ID;

For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:

   g_user_id      PLS_INTEGER  :=  fnd_global.user_id;
   g_login_id     PLS_INTEGER  :=  fnd_global.login_id;
   g_conc_req_id  PLS_INTEGER  :=  fnd_global.conc_request_id;
   g_org_id       PLS_INTEGER  :=  fnd_profile.value('ORG_ID');
   g_sob_id       PLS_INTEGER  :=  fnd_profile.value('GL_SET_OF_BKS_ID');

And initialize the application environment as follows:

   v_resp_appl_id  := fnd_global.resp_appl_id;
   v_resp_id       := fnd_global.resp_id;
   v_user_id       := fnd_global.user_id;
   
   FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);

Query to find all responsibilities of a user

SELECT fu.user_name                "User Name",
       frt.responsibility_name     "Responsibility Name",
       furg.start_date             "Start Date",
       furg.end_date               "End Date",     
       fr.responsibility_key       "Responsibility Key",
       fa.application_short_name   "Application Short Name"
  FROM fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
 WHERE furg.user_id             =  fu.user_id
   AND furg.responsibility_id   =  frt.responsibility_id
   AND fr.responsibility_id     =  frt.responsibility_id
   AND fa.application_id        =  fat.application_id
   AND fr.application_id        =  fat.application_id
   AND frt.language             =  USERENV('LANG')
   AND UPPER(fu.user_name)      =  UPPER('xxuser')
 ORDER BY frt.responsibility_name;

Supplier Conversion in Oracle Apps R12

In My scenario it is a conversion. Just one time loading Suppliers in Oracle Apps.
In short I will explain and attached the code below:

This is only one file having Supplier Headers, Sites and Supplier Contacts.
Based on the file developed one Stage table to store till Import into Interface Tables:

/* Stage Table */
create table XX_SUPP_INT_STG
(
  vendor_name                VARCHAR2(240),
  vendor_name_alt            VARCHAR2(320),
  legacy_vendor_num          VARCHAR2(30),
  vendor_type                VARCHAR2(30),
  organization_name          VARCHAR2(240),
  country                    VARCHAR2(25),
  address_line1              VARCHAR2(240),
  address_line2              VARCHAR2(240),
  address_line3              VARCHAR2(240),
  address_line4              VARCHAR2(240),
  city                       VARCHAR2(60),
  state                      VARCHAR2(60),
  province                   VARCHAR2(60),
  postal_code                VARCHAR2(20),
  vendor_site_code           VARCHAR2(15),
  purchasing_site            VARCHAR2(1),
  pay_site                   VARCHAR2(1),
  phone_area_code            VARCHAR2(10),
  phone_number               VARCHAR2(15),
  fax_area_code              VARCHAR2(10),
  fax                        VARCHAR2(15),
  site_email                 VARCHAR2(2000),
  allow_wht_tax_header_level VARCHAR2(1),
  invoice_wht_tax_group_hdr  NUMBER(15),
  allow_wht_tax_site_level   VARCHAR2(1),
  invoice_wht_tax_group_site NUMBER(15),
  calc_tax_site_level        VARCHAR2(1),
  vat_reg_number             VARCHAR2(20),
  vat_code                   VARCHAR2(30),
  hold_all_pay_hdr           VARCHAR2(1),
  hold_unmatch_inv_hdr       VARCHAR2(1),
  hold_unvali_inv_hdr        VARCHAR2(1),
  hold_reason_hdr            VARCHAR2(240),
  hold_all_pay_site          VARCHAR2(1),
  hold_unmatch_inv_site      VARCHAR2(1),
  hold_unvali_inv_site       VARCHAR2(1),
  hold_reason_site           VARCHAR2(240),
  pay_group                  VARCHAR2(25),
  prefix                     VARCHAR2(60),
  title                      VARCHAR2(30),
  first_name                 VARCHAR2(15),
  middle_name                VARCHAR2(15),
  last_name                  VARCHAR2(15),
  contact_email              VARCHAR2(2000),
  department                 VARCHAR2(60),
  contact_fax                VARCHAR2(40),
  contact_phone              VARCHAR2(40),
  contact_alt_phone          VARCHAR2(40)
) ;

We have interface tables for Supplier Headers, Sites and Contacts:

AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUPPLIER_SITES_INT

Base Tables:

AP_SUPPLIERS / PO_VENDORS
AP_SUPPLIER_SITES / PO_VENDOR_SITES_ALL
AP_SUPPLIER_CONTACTS / PO_VENDOR_CONTANCTS

I have above stage table columns in my excel data file, it is in .xls ( we can have in .csv also. if not in these two types, we need to format the file as per our process.

Dump the excel file Data into Stage table directly ( I have done this step using PL/SQL Developer).

After I have data in my stage table: I run the below code. Because of I have done this conversion one time only, it will never use in future, but if it is multi time process (Interface), just you can create the package or procedure, register in application as per your requirement you can go ahead and you can add more validations before importing into interface table and stage table with respect your business need.

======================================================================
DECLARE

  CURSOR C_SUPP_DATA is(
    SELECT distinct VENDOR_NAME,
                  VENDOR_NAME_ALT,
          LEGACY_VENDOR_NUM,
          VENDOR_TYPE,
          PAY_GROUP,
          ALLOW_WHT_TAX_HEADER_LEVEL,
          INVOICE_WHT_TAX_GROUP_HDR,
          HOLD_ALL_PAY_HDR,
          HOLD_UNMATCH_INV_HDR,
          HOLD_UNVALI_INV_HDR,
          HOLD_REASON_HDR
      FROM XX_SUPP_INT_STG);

  CURSOR C_SITE_DATA(P_VENDOR_NAME varchar2) is(
    SELECT distinct COUNTRY,
                    ADDRESS_LINE1,
                    ADDRESS_LINE2,
                    ADDRESS_LINE3,
                    ADDRESS_LINE4,
                    CITY,
                    STATE,
                    PROVINCE,
                    POSTAL_CODE,
                    VENDOR_SITE_CODE,
                    PURCHASING_SITE,
                    PAY_SITE,
                    PHONE_AREA_CODE,
                    PHONE_NUMBER,
                    FAX_AREA_CODE,
                    FAX,
                    SITE_EMAIL,
                    ALLOW_WHT_TAX_HEADER_LEVEL,
                    INVOICE_WHT_TAX_GROUP_HDR,
                    ALLOW_WHT_TAX_SITE_LEVEL,
                    INVOICE_WHT_TAX_GROUP_SITE,
                    CALC_TAX_SITE_LEVEL,
                    VAT_REG_NUMBER,
                    VAT_CODE,
                    HOLD_ALL_PAY_HDR,
                    HOLD_UNMATCH_INV_HDR,
                    HOLD_UNVALI_INV_HDR,
                    HOLD_REASON_HDR,
                    HOLD_ALL_PAY_SITE,
                    HOLD_UNMATCH_INV_SITE,
                    HOLD_UNVALI_INV_SITE,
                    HOLD_REASON_SITE,
                    PAY_GROUP,
ORGANIZATION_NAME
      FROM XX_SUPP_INT_STG
     where VENDOR_NAME = P_VENDOR_NAME);

  CURSOR C_CONTACT_DATA(P_VENDOR_NAME varchar2, P_VENDOR_SITE varchar2) is(
    select distinct PREFIX,
                    TITLE,
                    FIRST_NAME,
                    MIDDLE_NAME,
                    LAST_NAME,
                    CONTACT_EMAIL,
                    DEPARTMENT,
                    CONTACT_FAX,
                    CONTACT_PHONE,
                    CONTACT_ALT_PHONE
      from XX_SUPP_INT_STG
     where VENDOR_NAME = P_VENDOR_NAME
       and VENDOR_SITE_CODE = P_VENDOR_SITE);

  lc_site_org_id                 po_vendor_sites_all.org_id%type; -- := 50168; /* this need to remove*/
  lc_vendor_interface_id         ap_suppliers_int.vendor_interface_id%type;
  lc_vendor_site_interface_id    ap_supplier_sites_int.vendor_site_interface_id%type;
  lc_vendor_contact_interface_id ap_sup_site_contact_int.vendor_contact_interface_id%type;
  lc_vendor_cnt                  NUMBER := 0;
  lc_user_id                     NUMBER;

BEGIN

  FOR i IN C_SUPP_DATA LOOP
    /* Main Loop */

    lc_vendor_interface_id := ap_suppliers_int_s.nextval;
 
    begin
      INSERT INTO ap.ap_suppliers_int
        (vendor_interface_id,
         vendor_name,
         vendor_name_alt,
         vendor_type_lookup_code,
         allow_awt_flag,
         awt_group_id,
         hold_all_payments_flag,
         hold_unmatched_invoices_flag,
         hold_future_payments_flag,
         hold_reason,
         pay_group_lookup_code,
         attribute10)
      VALUES
        (lc_vendor_interface_id,
         i.vendor_name,
         i.vendor_name_alt,
         i.vendor_type,
         i.allow_wht_tax_header_level,
         i.invoice_wht_tax_group_hdr,
         i.hold_all_pay_hdr,
         i.hold_unmatch_inv_hdr,
         i.hold_unvali_inv_hdr,
         i.hold_reason_hdr,
         i.pay_group,
         i.legacy_vendor_num );
 
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(i.vendor_name ||
                             ' Error occured occured at Supplier Header ' ||
                             SQLCODE || ' - ' || SQLERRM);
   
    end;

    FOR j IN C_SITE_DATA(i.vendor_name) LOOP
 
BEGIN
       SELECT organization_id
       INTO lc_site_org_id
       FROM hr_operating_units
       WHERE NAME = j.organization_name ;
     
     EXCEPTION
       WHEN OTHERS
       THEN
       DBMS_OUTPUT.PUT_LINE(j.organization_name, 'invalid Organization');
    END;

      begin
        lc_vendor_site_interface_id := ap_supplier_sites_int_s.nextval;
   
        INSERT INTO ap.ap_supplier_sites_int
          (vendor_site_interface_id,
           vendor_interface_id,
           vendor_site_code,
           address_line1,
           address_line2,
           address_line3,
           address_line4,
           city,
           state,
           country,
           zip,
           phone,
           org_id,
           province,
           purchasing_site_flag,
           pay_site_flag,
           area_code,
           fax_area_code,
           fax,
           email_address,
           allow_awt_flag,
           awt_group_id,
           auto_tax_calc_flag,
           vat_registration_num,
           vat_code,
           hold_all_payments_flag,
           hold_future_payments_flag,
           HOLD_REASON)
        VALUES
          (lc_vendor_site_interface_id,
           lc_vendor_interface_id,
           j.vendor_site_code,
           j.address_line1,
           j.address_line2,
           j.address_line3,
           j.address_line4,
           j.city,
           j.state,
           j.country,
           j.postal_code,
           j.phone_number,
           lc_site_org_id,
           j.province,
           j.purchasing_site,
           j.pay_site,
           j.phone_area_code,
           j.fax_area_code,
           j.fax,
           j.site_email,
           j.allow_wht_tax_site_level,
           j.invoice_wht_tax_group_site,
           j.calc_tax_site_level,
           j.vat_reg_number,
           j.vat_code,
           j.hold_all_pay_site,
           j.hold_unvali_inv_site,
           j.HOLD_REASON_SITE);
   
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(i.vendor_name || ' ' || j.vendor_site_code ||
                               ' Error occured occured at Supplier Site ' ||
                               SQLCODE || ' - ' || SQLERRM);
     
      end;
 
      for k in C_CONTACT_DATA(i.vendor_name, j.vendor_site_code) LOOP
     
IF k.last_name IS NULL THEN --last name condition
   DBMS_OUTPUT.PUT_LINE(i.vendor_name||''||j.vendor_site_code||''||'Contact not created because LAST NAME is null ');
        ELSE
        begin
        lc_vendor_contact_interface_id :=ap_sup_site_contact_int_s.nextval ;
   
          INSERT INTO ap.ap_sup_site_contact_int ss
            (vendor_interface_id,
             vendor_contact_interface_id,
             vendor_site_code,
             first_name,
             middle_name,
             last_name,
             area_code,
             phone,
             first_name_alt,
             last_name_alt,
             email_address,
             fax,
             prefix,
             title,
             department,
             alt_phone,
             org_id)
          VALUES
            (lc_vendor_interface_id,
             lc_vendor_contact_interface_id,
             j.vendor_site_code,
             k.first_name,
             k.middle_name,
             k.last_name,
'',--area_code
             k.contact_phone,
             NULL,
             NULL,
             k.contact_email,
             k.contact_fax,
             k.prefix,
             k.title,
             k.department,
             NVL(k.contact_alt_phone,k.contact_phone),
             lc_site_org_id);
     
        EXCEPTION
          WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(i.vendor_name || ' ' ||
                                 j.vendor_site_code || ' ' || k.first_name ||
                                 ' Error occured at Supplier Contact ' ||
                                 SQLCODE || ' - ' || SQLERRM);
       
        END;

        END IF; --last name condition
      END LOOP; -- Contact Loop
 
    END LOOP; -- Site Loop

  END LOOP; -- Main Loop
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Interface ID: '||lc_vendor_interface_id);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unhandled Exception occured at Supplier Header ' ||
                         SQLCODE || ' - ' || SQLERRM);
END;
======================================================================

After data inserted into Interface tables, we need to run the concurrent programs one after another to get the data into base tables, as I said above one time process so, I did not created one package and call everything there, but you can do that, if required.

Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Contacts Open Interface Import

Once above programs completed Normal, you can see your migrated data in below base tables based on my legacy_vendor_number i.e

AP_SUPPLIERS / PO_VENDORS (attribute10 in this table )
AP_SUPPLIER_SITES / PO_VENDOR_SITES_ALL
AP_SUPPLIER_CONTACTS / PO_VENDOR_CONTANCTS

SQL Query to Get Vendor (Supplier) details with Supplier Header and Site Payment Methods in Oracle Apps

/* Supplier Detail Query */
SELECT
   pov.vendor_name
   ,pov.vendor_name_alt
   ,pov.segment1 vendor_number
   ,pov.vendor_type_lookup_code "VENDOR TYPE"
   ,pov.creation_date "VENDOR CREATION DATE"
   ,( SELECT
         paym.payment_method_code
      FROM
          ap_suppliers supp,
          iby_external_payees_all payee,
          iby_ext_party_pmt_mthds paym
      WHERE  1 = 1
      AND   supp.party_id = payee.payee_party_id
      AND   payee.ext_payee_id = paym.ext_pmt_party_id
      AND   supplier_site_id IS NULL
      AND   paym.primary_flag = 'Y'
      AND   pov.vendor_id = supp.vendor_id
    ) VENDOR_HDR_PAY_METHOD
   ,pov.end_date_active "VENDOR_END_DATE_ACTIVE"
   ,pov.vat_registration_num "VENDOR REGISTRATION NUMBER"
   ,pvs.vendor_site_code
   ,paym.payment_method_code VENDOR_SITE_PAY_METHOD
   ,pvs.end_date_active "VENDOR_SITE_END_DATE_ACTIVE"
   ,pvs.inactive_date "SITE INACTIVE DATE"
   ,hou.name "ORGANIZATION NAME"
   ,pvs.invoice_currency_code "INVOICE CURRENCY"
   ,pvs.payment_currency_code "PAYMENT CURRENCY"
   ,pvs.address_line1||' '||pvs.address_line2||' '||pvs.address_line1||' '||pvs.city||' '||pvs.state||' '||pvs.zip||' '||pvs.province||' '||pvs.country "VENDOR SITE ADDRESS"
   ,pvs.status "SITE STATUS"
   ,pvs.remittance_email "SITE REMITTANCE EMAIL"
   ,pvs.email_address "SITE EMAIL"
   ,pvs.fax_area_code||' '||pvs.fax "SITE FAX"
   ,pvc.prefix
   ,pvc.title
   ,pvc.first_name
   ,pvc.middle_name
   ,pvc.last_name
   ,pvc.email_address "CONTACT EMAIL"
   ,pvc.DEPARTMENT
   ,pvc.fax_area_code||' '||pvc.fax "CONTACT FAX"
   ,pvc.area_code||' '||pvc.phone "CONTACT PHONE"
   ,pvc.alt_area_code||' '||pvc.alt_phone "CONTACT ALT PHONE"
   ,pvc.inactive_date "CONTACT INACTIVE DATE"
FROM
   po_vendors pov
   ,po_vendor_sites_all pvs
   ,po_vendor_contacts pvc
   ,hr_operating_units hou
   ,ap_suppliers supp
   ,ap_supplier_sites_all sites
   ,iby_external_payees_all payee
   ,iby_ext_party_pmt_mthds paym
WHERE 1 = 1
AND   pov.vendor_id = pvs.vendor_id
AND   pvs.vendor_site_id = pvc.vendor_site_id
AND   pov.vendor_id = pvc.vendor_id
AND   pvs.org_id = hou.organization_id
AND   pvs.status = 'A'
AND   pov.vendor_type_lookup_code !='EMPLOYEE'
AND   ((TO_CHAR(pov.end_date_active,'DD-MM-YYYY') <= TO_CHAR(SYSDATE,'DD-MM-YYYY') ) OR pov.end_date_active IS NULL OR TO_CHAR(pov.end_date_active,'DD-MM-YYYY') = '31-12-4712' )
AND   ((TO_CHAR(pvs.end_date_active,'DD-MM-YYYY') <= TO_CHAR(SYSDATE,'DD-MM-YYYY') ) OR pvs.end_date_active IS NULL OR TO_CHAR(pvs.end_date_active,'DD-MM-YYYY') = '31-12-4712' )
AND   ((TO_CHAR(pvs.inactive_date,'DD-MM-YYYY') <= TO_CHAR(SYSDATE,'DD-MM-YYYY') ) OR pvs.inactive_date IS NULL OR TO_CHAR(pvs.inactive_date,'DD-MM-YYYY') = '31-12-4712' )
AND   ((TO_CHAR(pvc.inactive_date,'DD-MM-YYYY') <= TO_CHAR(SYSDATE,'DD-MM-YYYY') ) OR pvc.inactive_date IS NULL OR TO_CHAR(pvc.inactive_date,'DD-MM-YYYY') = '31-12-4712' )
AND   supp.party_id = payee.payee_party_id
AND   payee.ext_payee_id = paym.ext_pmt_party_id
AND   supp.vendor_id = sites.vendor_id
AND   sites.vendor_site_id = payee.supplier_site_id
AND   paym.primary_flag = 'Y'
AND   pov.vendor_id = supp.vendor_id
AND   pvs.vendor_site_id = sites.vendor_site_id
ORDER BY pov.segment1 ASC

SQL Query to get Open, Approved Blanket Agreement Purchase Orders in Oracle Apps

/* Open Blanket Agreement Query*/
SELECT
   pha.segment1 "BLANKET AGREEMENT NUMBER"
   ,pha.revision_num
   ,pha.creation_date "AGREEMENT CREATION DATE"
   ,plc.displayed_field "AGREEMENT TYPE"
   ,pha.authorization_status "APPROVAL STATUS"
   ,pha.blanket_total_amount "AMOUNT"
   ,pha.approved_date
   ,NVL(INITCAP(pha.closed_code),'Open') "AGREEMENT STATUS"
   ,pla.line_num "PO LINE NUMBER"
   ,pla.creation_date "LINE CREATION DATE"
   ,NVL(pla.closed_code,'Open') "LINE STATUS"
   ,hou.name "ORGANIZATION NAME"
   ,pov.segment1 "VENDOR NUMBER"
   ,pov.VENDOR_NAME
   ,pov.VENDOR_TYPE_LOOKUP_CODE "VENDOR TYPE"
   ,pvs.VENDOR_SITE_CODE
   ,pha.currency_code
   ,ppf.full_name "BUYER NAME"
   ,pha.attribute_category "CATEGORY"
   ,pha.attribute1
   ,msi.segment1 "LINE ITEM"
   ,msi.description "ITEM DESCRIPTION"
   ,mcb.segment1||'.'||mcb.segment2||'.'||mcb.segment3||'.'||mcb.segment1 "ITEM CATEGORY"
   ,pla.quantity "BA QUANTITY"
   ,pla.unit_meas_lookup_code "UOM"
   ,pla.unit_price
FROM
   apps.po_headers_all pha
   ,apps.po_lines_all pla
   ,apps.po_lookup_codes plc
   ,apps.po_vendors pov
   ,apps.po_vendor_sites_all pvs
   ,apps.per_all_people_f ppf
   ,apps.hr_operating_units hou
   ,apps.mtl_system_items_b msi 
   ,apps.mtl_categories_b mcb
WHERE 1 = 1
AND   pha.po_header_id = pla.po_header_id
AND   pha.type_lookup_code = plc.lookup_code
AND   pha.vendor_id = pov.vendor_id
AND   pov.vendor_id = pvs.vendor_id
AND   pha.vendor_site_id =pvs.VENDOR_SITE_ID
AND   pha.agent_id = ppf.person_id
AND   (SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date  )
AND   pha.org_id = hou.organization_id
AND   pla.item_id = msi.inventory_item_id
AND   msi.organization_id = 63
AND   pla.category_id = mcb.category_id
AND   plc.lookup_type = 'AGREEMENT_TYPE'
AND   pha.type_lookup_code = 'BLANKET'
AND   NVL(pha.closed_code,'OPEN') = 'OPEN'
AND   pha.authorization_status = 'APPROVED'
ORDER BY pha.segment1

AP Invoice Approval Status Tracking:

AP Invoice Approval Status Tracking:

Invoice Validation
Invoice Approval Workflow

select a.invoice_id,a.invoice_num, a.invoice_amount, a.approval_ready_flag, a.approval_iteration, a.wfapproval_status
from
ap_invoices_all a
where invoice_num = 'INV_TEST_2' ;

select * from
ap_inv_aprvl_hist_all
where invoice_id = 53931065 ;

select * from
wf_notifications
where item_key like '53931065%'
and message_type = 'APINVAPR';

SQL Query to get Oracle Value Set definition

Query 1:

select b.flex_value_set_name,a.additional_where_clause from FND_FLEX_VALIDATION_TABLES a, fnd_flex_value_sets b
where a.application_table_name = 'MTL_SECONDARY_INVENTORIES'
and a.value_column_name ='SECONDARY_INVENTORY_NAME'
and a.flex_value_set_id = b.flex_value_set_id



Query 2:

SELECT ffvs.flex_value_set_id ,
       ffvs.flex_value_set_name ,
       ffvs.description set_description ,
       ffvs.validation_type,
       ffvt.value_column_name ,
       ffvt.meaning_column_name ,
       ffvt.id_column_name ,
       ffvt.application_table_name ,
       ffvt.additional_where_clause
FROM   APPS.fND_FLEX_VALUE_SETS FFVS ,
       APPS.FND_FLEX_VALIDATION_TABLES FFVT
WHERE  ffvs.flex_value_set_id = ffvt.flex_value_set_id
   AND ffvs.flex_value_set_name IN ('VALUE SET NAME')

Generate Spool for multiple db objects / How to generate Spool for multiple DB objects in oracle

SET PAGES 0

SET TIMING   OFF
SET ECHO     OFF
SET FEEDBACK OFF

SET TERMOUT  OFF

SPOOL C:\Emirates\Assignments\views\manyspools_8.sql


SELECT 'SPOOL C:\Emirates\Assignments\objects\' || object_name || '.sql' || CHR (10) ||
       'SELECT text from dba_source WHERE name = ''' || object_name || ''';' || CHR (10) ||
       'SPOOL OFF'
  from xxtemp_hr_stg where object_type in ('PROCEDURE','PACKAGE','PACKAGE BODY','FUNCTION');

SPOOL OFF

@ C:\Emirates\Assignments\objects\manyspools_8.sql

SET TERMOUT ON

--Note:

-- This script can run from SQL Developer / TOAD / PLSQL Developer
-- In above script, instead of table "dba_source", need to use dba_views for views

--My stage table: where my list of objects stored in stage table

--xxtemp_hr_stg

--Table Script:

create table xxtemp_hr_stg

(

 SNO NUMBER,

 OWNER VARCHAR2(10),

 OBJECT_NAME VARCHAR2(100),

 OBJECT_TYPE VARCHAR2(20),

 REMARKS VARCHAR2(2000),

 MODIFY_YN VARCHAR2(2000),

 ADDITIONAL_COMMENTS VARCHAR2(4000)

);

Generate Spool file for Single (particular DB Object) / How to generate the spool for db objects in oracle

set feedback off
set heading off
set timing off
set termout off
set linesize 1000
set trimspool on
set verify off
spool C:\YADUL\FILES_220519\XX_DB_OBJECT_NAME.sql
prompt set define off
select text
    from dba_source
    where name = upper('XX_DB_OBJECT_NAME')
    order by type, line;
prompt /
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100
set timing on

--Note:
-- This script can run from SQL Developer / TOAD / PLSQL Developer
-- In above script, instead of this table "dba_source", need to user dba_views for views

SQL Query to find Responsibility for given Request group / Find Request group attached responsibilities

SELECT responsibility_name ,
  request_group_name        ,
  frg.description
   FROM fnd_request_groups frg,
  fnd_responsibility_vl frv
  WHERE frv.request_group_id = frg.request_group_id
AND request_group_name    LIKE 'Request group name'
ORDER BY responsibility_name;

SQL Query to find Concurrent program details with Parameters, status, submitted date and responsibility

SELECT
    cpt.user_concurrent_program_name   "Concurrent Program Name",
    decode(rgu.request_unit_type, 'P', 'Program', 'S', 'Set',
           rgu.request_unit_type) "Unit Type",
    fcr.status_code                    status,
    fcr.request_date                   prog_submitted_date,
    fnr.responsibility_name,
    fcr.argument1
    || '-'
    || fcr.argument2
    || '-'
    || fcr.argument3
    || '-'
    || fcr.argument4
    || '-'
    || fcr.argument5
    || '-'
    || fcr.argument6
    || '-'
    || fcr.argument7
    || '-'
    || fcr.argument8
    || '-'
    || fcr.argument9
    || '-'
    || fcr.argument10
    || '-'
    || fcr.argument11
    || '-'
    || fcr.argument12
    || '-'
    || fcr.argument13
    || '-'
    || fcr.argument14
    || '-'
    || fcr.argument15 parameters,
    cp.concurrent_program_name         "Concurrent Program Short Name",
    rg.application_id                  "Application ID",
    rg.request_group_name              "Request Group Name",
    fat.application_name               "Application Name",
    fa.application_short_name          "Application Short Name",
    fa.basepath                        "Basepath",
    cpt.concurrent_program_id
FROM
    fnd_request_groups           rg,
    fnd_request_group_units      rgu,
    fnd_concurrent_programs      cp,
    fnd_concurrent_programs_tl   cpt,
    fnd_application              fa,
    fnd_application_tl           fat--,
    ,
    fnd_concurrent_requests      fcr,
    fnd_responsibility_tl        fnr
WHERE
    rg.request_group_id = rgu.request_group_id
    AND rgu.request_unit_id = cp.concurrent_program_id
    AND cp.concurrent_program_id = cpt.concurrent_program_id
    AND rg.application_id = fat.application_id
    AND fa.application_id = fat.application_id
    AND cpt.language = userenv('LANG')
    AND fat.language = userenv('LANG')
    AND cpt.concurrent_program_id = fcr.concurrent_program_id
    AND fcr.responsibility_id = fnr.responsibility_id
    AND cpt.user_concurrent_program_name = 'Concurrent program name'
ORDER BY
    fcr.request_date DESC

How to find password of a User in Oracle Apps R12

--Package Specification

CREATE OR REPLACE PACKAGE get_pwd

AS

   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

      RETURN VARCHAR2;

END get_pwd;

/

--Package Body
CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

END get_pwd;
/

--Query to execute
SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = '&USER_NAME';

How to find patches applied/installed on Oracle database

SELECT
e.patch_name,
c.end_date,
a.bug_number,
b.applied_flag
FROM
ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d ,
ad_applied_patches e
WHERE
AND a.bug_id = b.bug_id
AND b.patch_run_id = c.patch_run_id
AND c.patch_driver_id = d.patch_driver_id

AND d.applied_patch_id = e.applied_patch_id

For Oracle 11g Release:

1. using Opatch utility

cd $ORACLE_HOME/OPatch

opatch lsinventory

2.  select * from sys.registry$history;



For Oracle 12c Release:

1. Opatch utility

cd $ORACLE_HOME/OPatch

opatch lsinventory

2. using dba_registry_sqlpatch view:

SQL> select * from dba_registry_sqlpatch;

3. using package dbms_qopatch

SQL> set serverout on

SQL> exec dbms_qopatch.get_sqlpatch_status;

Profile to setup the language for Oracle Tools in Local Machine

Profile to setup the language for Oracle Tools in Local Machine

Start -> Run (CMD) -> REGEDIT --> Enter,

Then search for specific Tool (Report Builder, Workflow (WF) or ..etc)

API to Cancel Approved Purchase Order (In whole PO or Particular PO Line)

API to Cancel Approved Purchase Order

Complete PO:

DECLARE
l_return_status VARCHAR2(1);
BEGIN
fnd_global.apps_initialize(1053,50578,201);
-- mo_global.init('PO'); -- need for R12

--call the Cancel API for PO
PO_Document_Control_PUB.control_document (
1.0, -- p_api_version
FND_API.G_TRUE, -- p_init_msg_list
FND_API.G_TRUE, -- p_commit
l_return_status,-- x_return_status
'PO', -- p_doc_type
'STANDARD', -- p_doc_subtype
null, -- p_doc_id
'23975', -- p_doc_num
null, -- p_release_id
null, -- p_release_num
null, -- p_doc_line_id
null, -- p_doc_line_num
null, -- p_doc_line_loc_id
null, -- p_doc_shipment_num
'CANCEL', -- p_action
SYSDATE, -- p_action_date
null, -- p_cancel_reason
'N', -- p_cancel_reqs_flag
null, -- p_print_flag
null ); -- p_note_to_vendor

-- Get any messages returned by the Cancel API

FOR i IN 1..FND_MSG_PUB.count_msg
LOOP
DBMS_OUTPUT.put_line(FND_MSG_PUB.Get(p_msg_index => i,
p_encoded => 'F'));
END LOOP;
END;

Specific Line :

DECLARE
l_return_status VARCHAR2(1);
BEGIN
fnd_global.apps_initialize(1053,50578,201);
-- mo_global.init('PO'); -- need for R12

--call the Cancel API for PO
PO_Document_Control_PUB.control_document (
1.0, -- p_api_version
FND_API.G_TRUE, -- p_init_msg_list
FND_API.G_TRUE, -- p_commit
l_return_status,-- x_return_status
'PO', -- p_doc_type
'STANDARD', -- p_doc_subtype
null, -- p_doc_id
'23975', -- p_doc_num
null, -- p_release_id
null, -- p_release_num
null, -- p_doc_line_id
'1', -- p_doc_line_num
null, -- p_doc_line_loc_id
null, -- p_doc_shipment_num
'CANCEL', -- p_action
SYSDATE, -- p_action_date
null, -- p_cancel_reason
'N', -- p_cancel_reqs_flag
null, -- p_print_flag
null ); -- p_note_to_vendor

-- Get any messages returned by the Cancel API

FOR i IN 1..FND_MSG_PUB.count_msg
LOOP
DBMS_OUTPUT.put_line(FND_MSG_PUB.Get(p_msg_index => i,
p_encoded => 'F'));
END LOOP;
END;

Finding Nth highest salary in a table

Finding Nth highest salary in a table, Here is a way to do this task using dense_rank() function.


select * from(
select ename, sal, dense_rank()
over(order by sal desc)r from Employee)
where r=&n;


DENSE_RANK :

1. DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1.

2. This function accepts arguments as any numeric data type and returns NUMBER.

3. As an analytic function, DENSE_RANK computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the order_by_clause.
 
4. In the above query the rank is returned based on sal of the employee table. In case of tie, it assigns equal rank to all the rows.

OAF VO Extension Steps for Migration to Instance

java oracle.jrad.tools.xml.importer.JPXImporter /u01/app/EBSPRD/fs1/FMW_Home/Oracle_EBS-app1/applications/oacore/html/WEB-INF/classes/UDC_OAProject.jpx -username apps -password apps -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oaprddb01-vip.udcqatar.com)(PORT=1536))(CONNECT_DATA=(SERVICE_NAME=EBSPRD)(INSTANCE_NAME=EBSPRD1))

AME Material

http://mandeeporacle.blogspot.com/2014/05/ame-setups.html


http://ebs4learner.blogspot.com/

Python Full Course

Python Full Course;


https://www.python.org/

Python Tutorials for Beginners

Python Tutorials for Beginners


https://www.tutorialspoint.com/python/python_online_test.htm

Few XML Tags for RTF Development

<?for-each:G_INVENTORY_ITEM_ID?><?sort:ITEM_CODE;'ascending';data-type='text'?>

<?for-each:G_PO_LINE_NUM?><?sort:PO_LINE_NUM;'ascending';data-type='number'?>


<?if:INVOICE_ID!=''?> <?end if?>

Example of $FLEX$ Syntax Used In Value Set

Example of $FLEX$ Syntax Used In Value Set ($flex$ in oracle apps)

Example of using “:$FLEX$.Value_Set_Name” to set up value sets where one segment depends on a prior segment that itself depends on a prior segment. Suppose you have a three-segment flexfield where the first segment is Country, the second segment is State, and the third segment is District. You could limit your third segment's values to only include Districts that are available for the Address specified in the first two segments. Your three value sets might be defined as follows: 

 

Segment Name              Country_Segment

Value Set Name             Country_Value_Set

Validation Table             Country_Table

Value Column               Country_NAME

Description Column     Country_DESCRIPTION

Hidden ID Column       Country_ID

SQL Where Clause       (none)

 

Segment Name             State_Segment

Value Set Name            State_Value_Set

Validation Table            State_Table

Value Column              State_Name

Description Column    State_DESCRIPTION

Hidden ID Column       State_ID

SQL Where Clause      WHERE Country_ID = :$FLEX$.Country_Value_Set

 

Segment Name            District_Segment

Value Set Name           District_Value_Set

Validation Table           District_Table

Value Column              District_NAME

Description Column   District_DESCRIPTION

Hidden ID Column     District_ID

SQL Where Clause     WHERE Country_ID = :$FLEX$. Country_Value_Set

                                                                               AND Country_ID = :$FLEX$.State_Value_Set 

 

In this example, Country_ID is the hidden ID column and Country_Name is the value column of the Country_Value_Set value set. The Model segment uses the hidden ID column of the previous value set, Country_Value_Set, to compare against its WHERE clause. The end user never sees the hidden ID value for this example.

 

"Example of $FLEX$ Syntax Used in Oracle Apps ($flex$ in oracle apps) "
 

Object Oriented Concepts in Java

Object Oriented Concepts in Java


https://www.csestack.org/object-oriented-concepts-java-example-code/

Registry for Multi Language Setup in Oracle Report Builder

Windows --> Start --> Run --> REGEDIT.


find 'NLS_LANG' and make the same id for all the places of NLS_LANG, then it will work for the languages like:


English, Arabic ( As I tested)


Issue was:


When I am passing the Arabic words in the Query it was displaying "?" question marks, instead of data, to over come this, I have done above changes in Registry, so it is working. 

SQL Query to find duplicate values in a table

SQL Query to find duplicate values in a table


SELECT
col,
COUNT(col)
FROM.
table_name.
GROUP BY col.
HAVING COUNT(col) > 1;

Query to find Request Group for concurrent program

Query to find Request Group for concurrent program






SELECT cpt.user_concurrent_program_name     "Concurrent Program Name",
       DECODE(rgu.request_unit_type,
              'P', 'Program',
              'S', 'Set',
              rgu.request_unit_type)        "Unit Type",
       cp.concurrent_program_name           "Concurrent Program Short Name",
       rg.application_id                    "Application ID",
       rg.request_group_name                "Request Group Name",
       fat.application_name                 "Application Name",
       fa.application_short_name            "Application Short Name",
       fa.basepath                          "Basepath"
  FROM fnd_request_groups          rg,
       fnd_request_group_units     rgu,
       fnd_concurrent_programs     cp,
       fnd_concurrent_programs_tl  cpt,
       fnd_application             fa,
       fnd_application_tl          fat
 WHERE rg.request_group_id       =  rgu.request_group_id
   AND rgu.request_unit_id       =  cp.concurrent_program_id
   AND cp.concurrent_program_id  =  cpt.concurrent_program_id
   AND rg.application_id         =  fat.application_id
   AND fa.application_id         =  fat.application_id
   AND cpt.language              =  USERENV('LANG')
   AND fat.language              =  USERENV('LANG')
   AND cpt.user_concurrent_program_name = 'xx concurrent program name';

Main Page XML

<?xml version = '1.0' encoding = 'UTF-8'?>
<page xmlns:jrad="http://xmlns.oracle.com/jrad" xmlns:oa="http://xmlns.oracle.com/oa" xmlns:ui="http://xmlns.oracle.com/uix/ui" version="10.1.3_1312" xml:lang="en-US" xmlns:user="http://xmlns.oracle.com/jrad/user" xmlns="http://xmlns.oracle.com/jrad" file-version="$Header$">
   <content>
      <oa:pageLayout id="PageLayoutRN" amDefName="xxfin.oracle.apps.pos.deptreqsumm.server.xxdeptreqsummAM" windowTitle="Payment Batch Details" title="Payment Batch Details" controllerClass="xxfin.oracle.apps.pos.deptreqsumm.webui.xxdeptreqsummCO">
         <ui:corporateBranding>
            <oa:image id="corporateBrandingImage" source="/OA_MEDIA/FNDSSCORP.gif"/>
         </ui:corporateBranding>
         <ui:contents>
            <oa:rowLayout id="RowLayoutRN" width="50%">
               <ui:contents>
                  <oa:spacer id="item1" width="260"/>
                  <oa:messageLovInput id="PaymentBatchParam" prompt="Payment Batch" queryable="true" sortState="ascending" externalListOfValues="/xxfin/oracle/apps/pos/deptreqsumm/lov/webui/xxpaymentbatchlovRN" selectiveSearchCriteria="true" columns="30" required="yes">
                     <lovMappings>
                        <lovMap id="lovMap1" lovItem="CheckrunName" resultTo="PaymentBatchParam" criteriaFrom="PaymentBatchParam"/>
                        <lovMap id="lovMap2" lovItem="CheckrunId" resultTo="PaymentBatchId" criteriaFrom="PaymentBatchId"/>
                     </lovMappings>
                  </oa:messageLovInput>
                  <oa:submitButton id="SearchButton" text="Search" prompt="Search"/>
               </ui:contents>
            </oa:rowLayout>
            <oa:rowLayout id="region1">
               <ui:contents>
                  <oa:spacer id="item134" width="260"/>
                  <oa:tip id="item133" text="Plese provide the value for Input Parameter Payment Batch" comment="Plese provide the value for Input Parameter Payment Batch"/>
               </ui:contents>
            </oa:rowLayout>
            <oa:spacer id="Spacer" height="10" width="100"/>
            <oa:hideShowHeader id="SuppSummRN" text="Supplier Summary" disclosed="true">
               <ui:contents>
                  <oa:advancedTable id="xxsuppsummVO" viewName="xxsuppsummVO" width="75%" tableBanding="rowBanding" bandingInterval="1">
                     <ui:contents>
                        <oa:column id="SupplierNameCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="SupplierNameHdr" prompt="Supplier Name" sortState="yes"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="SupplierName" viewAttr="SupplierName"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="SupplierNumberCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="SupplierNumberHdr" prompt="Supplier Number" sortState="yes"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="SupplierNumber" viewAttr="SupplierNumber"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="TotalPaymentsCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="TotalPaymentsHdr" prompt="Total Payment" sortState="yes"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="TotalPayments" viewAttr="TotalPayments" dataType="NUMBER"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="NoOfPaymentsCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="NoOfPaymentsHdr" prompt="No.of Payments" sortState="yes"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="NoOfPayments" viewAttr="NoOfPayments"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="NoOfInvoicesCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="NoOfInvoicesHdr" prompt="No.of Invoices" sortState="yes"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="NoOfInvoices" viewAttr="NoOfInvoices"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="NoOfPosCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="NoOfPosHdr" prompt="No.of POs" sortState="yes"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="NoOfPos" viewAttr="NoOfPos"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="NoOfPrsCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="NoOfPrsHdr" prompt="No.of PRs" sortState="yes"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="NoOfPrs" viewAttr="NoOfPrs"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="PaymentBatchCol" rendered="false">
                           <ui:columnHeader>
                              <oa:sortableHeader id="PaymentBatchHdr" rendered="false"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageTextInput id="PaymentBatch" viewAttr="PaymentBatch" rendered="false"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="PaymentBatchIdCol" rendered="false">
                           <ui:columnHeader>
                              <oa:sortableHeader id="PaymentBatchIdHdr" rendered="false"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageTextInput id="PaymentBatchId" viewAttr="PaymentBatchId" rendered="false"/>
                           </ui:contents>
                        </oa:column>
                     </ui:contents>
                     <ui:tableSelection>
                        <oa:singleSelection id="SuppSingleSelection" serverUnvalidated="true" viewAttr="SuppSelectFlag">
                           <ui:primaryClientAction>
                              <ui:firePartialAction event="supplierSelect"/>
                           </ui:primaryClientAction>
                        </oa:singleSelection>
                     </ui:tableSelection>
                  </oa:advancedTable>
               </ui:contents>
            </oa:hideShowHeader>
            <oa:hideShowHeader id="SuppPaymentsRN" text="Payments" disclosed="true">
               <ui:contents>
                  <oa:advancedTable id="xxsupppaymentsVO" viewName="xxsupppaymentsVO" width="100%" tableBanding="rowBanding" bandingInterval="1">
                     <ui:contents>
                        <oa:column id="PaymentReferenceCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="PaymentReferenceHdr" prompt="Payment Reference"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="PaymentReference" viewAttr="PaymentReference" prompt="Payment Reference"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="PaymentDateCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="PaymentDateHdr" prompt="Payment Date"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="PaymentDate" viewAttr="PaymentDate" dataType="DATE"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="GlDateCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="GlDateHdr" prompt="GL Date"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="GlDate" viewAttr="GlDate" dataType="DATE"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="PaymentAmountCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="PaymentAmountHdr" prompt="Payment Amount"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="PaymentAmount" viewAttr="PaymentAmount" dataType="NUMBER"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="PaymentCurrencyCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="PaymentCurrencyHdr" prompt="Payment Currency"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="PaymentCurrency" viewAttr="PaymentCurrency"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="BankAccountCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="BankAccountHdr" prompt="Bank Account"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="BankAccount" viewAttr="BankAccount"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="AccountCurrencyCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="AccountCurrencyHdr" prompt="Account Currency"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="AccountCurrency" viewAttr="AccountCurrency"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="ExchangeRateCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="ExchangeRateHdr" prompt="Exchange Rate"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="ExchangeRate" viewAttr="ExchangeRate"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="RemitToAccountCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="RemitToAccountHdr" prompt="Remit To Account"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="RemitToAccount" viewAttr="RemitToAccount"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="PaymentAttachment" columnDataFormat="objectNameFormat">
                           <ui:columnHeader>
                              <oa:sortableHeader id="sortableHeader211" prompt="Payment Attachments"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:attachmentImage id="PayAtt" searchRegionRendered="false" prompt="Attachments">
                                 <oa:entityMappings>
                                    <oa:entityMap id="entityMap111" insertAllowed="false" updateAllowed="false" deleteAllowed="false" entityId="AP_CHECKS" showAll="true">
                                       <oa:primaryKeys>
                                          <oa:primaryKey id="primaryKey111" viewAttr="CheckId"/>
                                       </oa:primaryKeys>
                                       <oa:categoryMappings>
                                          <oa:categoryMap id="categoryMap111" categoryName="Payables"/>
                                          <oa:categoryMap id="categoryMap211" categoryName="Buyer"/>
                                          <oa:categoryMap id="categoryMap601" categoryName="Approver"/>
                                          <oa:categoryMap id="categoryMap602" categoryName="REQ Internal"/>
                                          <oa:categoryMap id="categoryMap603" categoryName="RFQ Internal"/>
                                          <oa:categoryMap id="categoryMap604" categoryName="FromSupplier"/>
                                          <oa:categoryMap id="categoryMap605" categoryName="Vendor"/>
                                          <oa:categoryMap id="categoryMap606" categoryName="Quote Internal"/>
                                          <oa:categoryMap id="categoryMap607" categoryName="Receiver"/>
                                          <oa:categoryMap id="categoryMap608" categoryName="Item Internal"/>
                                       </oa:categoryMappings>
                                    </oa:entityMap>
                                 </oa:entityMappings>
                              </oa:attachmentImage>
                           </ui:contents>
                        </oa:column>
                     </ui:contents>
                     <ui:tableSelection>
                        <oa:singleSelection id="PaySingleSelection" viewAttr="PaySelectFlag">
                           <ui:primaryClientAction>
                              <ui:firePartialAction event="paymentSelect"/>
                           </ui:primaryClientAction>
                        </oa:singleSelection>
                     </ui:tableSelection>
                  </oa:advancedTable>
               </ui:contents>
            </oa:hideShowHeader>
            <oa:hideShowHeader id="SuppInvoicesRN" text="Invoices" disclosed="true">
               <ui:contents>
                  <oa:advancedTable id="xxsuppinvVO" viewName="xxsupplierinvoicesVO" width="100%" tableBanding="rowBanding">
                     <ui:contents>
                        <oa:column id="InvoiceNoCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="InvoiceNoHdr" prompt="Invoice No"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="InvoiceNo" viewAttr="InvoiceNo"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="InvoiceDateCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="InvoiceDateHdr" prompt="Invoice Date"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="InvoiceDate" viewAttr="InvoiceDate"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="InvGlDateCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="InvGlDateHdr" prompt="GL Date"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="InvGlDate" viewAttr="GlDate"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="InvoiceCurrencyCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="InvoiceCurrencyHdr" prompt="Invoice Currency"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="InvoiceCurrency" viewAttr="InvoiceCurrency"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="InvoiceAmountCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="InvoiceAmountHdr" prompt="Invoice Amount"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="InvoiceAmount" viewAttr="InvoiceAmount"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="TaxAmountCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="TaxAmountHdr" prompt="Tax Amount"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="TaxAmount" viewAttr="TaxAmount"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="TotalInvoiceAmountCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="TotalInvoiceAmountHdr" prompt="Invoice Total Amount"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="TotalInvoiceAmount" viewAttr="TotalInvoiceAmount"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="InvNoOfPosCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="InvNoOfPosHdr" prompt="No.of Pos"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="InvNoOfPos" viewAttr="NoOfPos"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="InvAttachments" columnDataFormat="objectNameFormat">
                           <ui:columnHeader>
                              <oa:sortableHeader id="sortableHeader21" prompt="Invoice Attachments"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:attachmentImage id="InvAtt" searchRegionRendered="false" prompt="Attachments1">
                                 <oa:entityMappings>
                                    <oa:entityMap id="entityMap11" insertAllowed="false" updateAllowed="false" deleteAllowed="false" entityId="AP_INVOICES" showAll="true">
                                       <oa:primaryKeys>
                                          <oa:primaryKey id="primaryKey11" viewAttr="InvoiceId"/>
                                       </oa:primaryKeys>
                                       <oa:categoryMappings>
                                          <oa:categoryMap id="categoryMap11" categoryName="FromSupplier"/>
                                          <oa:categoryMap id="categoryMap21" categoryName="MISC"/>
                                          <oa:categoryMap id="categoryMap60" categoryName="Payables"/>
                                       </oa:categoryMappings>
                                    </oa:entityMap>
                                 </oa:entityMappings>
                              </oa:attachmentImage>
                           </ui:contents>
                        </oa:column>
                     </ui:contents>
                     <ui:tableSelection>
                        <oa:singleSelection id="InvSingleSelection" viewAttr="InvSelectFlag">
                           <ui:primaryClientAction>
                              <ui:firePartialAction event="invoiceSelect"/>
                           </ui:primaryClientAction>
                        </oa:singleSelection>
                     </ui:tableSelection>
                  </oa:advancedTable>
               </ui:contents>
            </oa:hideShowHeader>
            <oa:hideShowHeader id="SuppInvPosRN" text="Purchase Orders" disclosed="true">
               <ui:contents>
                  <oa:advancedTable id="xxsuppinvposVO" width="100%" viewName="xxsuppinvposVO" tableBanding="rowBanding">
                     <ui:contents>
                        <oa:column id="PonumCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="PonumHdr" prompt="PONum"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="Ponum" viewAttr="Ponum"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="PoDateCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="PoDateHdr" prompt="PO Date"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="PoDate" dataType="DATE" viewAttr="PoDate"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="PoCurrencyCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="PoCurrencyHdr" prompt="PO Currency"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="PoCurrency" viewAttr="PoCurrency"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="PoTotalCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="PoTotalHdr" prompt="PO Total"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="PoTotal" viewAttr="PoTotal"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="PoLineNumCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="PoLineNumHdr" prompt="PO Line Num"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="PoLineNum" viewAttr="PoLineNum"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="ItemDescriptionCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="sortableHeader1" prompt="Item Description"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="ItemDescription" viewAttr="ItemDescription"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="QtyCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="QtyHdr" prompt="Qty"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="Qty" viewAttr="Qty"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="PriceCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="PriceHdr" prompt="Price"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="Price" viewAttr="Price"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="LineAmoountCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="LineAmoountHdr" prompt="Line Amoount"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="LineAmoount" viewAttr="LineAmoount"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="POAttachments" columnDataFormat="objectNameFormat">
                           <ui:columnHeader>
                              <oa:sortableHeader id="sortableHeader2" prompt="PO Attachments"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:attachmentImage id="POAtt" searchRegionRendered="false">
                                 <oa:entityMappings>
                                    <oa:entityMap id="entityMap1" insertAllowed="false" updateAllowed="false" deleteAllowed="false" entityId="PO_HEADERS" showAll="true">
                                       <oa:primaryKeys>
                                          <oa:primaryKey id="primaryKey1" viewAttr="PoHeaderId"/>
                                       </oa:primaryKeys>
                                       <oa:categoryMappings>
                                          <oa:categoryMap id="categoryMap1" categoryName="Vendor"/>
                                          <oa:categoryMap id="categoryMap2" categoryName="Approver"/>
                                          <oa:categoryMap id="categoryMap5" categoryName="Buyer"/>
                                       </oa:categoryMappings>
                                    </oa:entityMap>
                                 </oa:entityMappings>
                              </oa:attachmentImage>
                           </ui:contents>
                        </oa:column>
                     </ui:contents>
                     <ui:tableSelection>
                        <oa:singleSelection id="PoSingleSelection" viewAttr="PoSelectFlag">
                           <ui:primaryClientAction>
                              <ui:firePartialAction event="poSelect">
                                 <ui:parameters>
                                    <ui:parameter key="tes1" value="${oa.xxsuppsummVO.SupplierId}"/>
                                 </ui:parameters>
                              </ui:firePartialAction>
                           </ui:primaryClientAction>
                        </oa:singleSelection>
                     </ui:tableSelection>
                  </oa:advancedTable>
               </ui:contents>
            </oa:hideShowHeader>
            <oa:hideShowHeader id="SuppInvPoPrsRN" text="Purchase Requisitions" disclosed="true">
               <ui:contents>
                  <oa:advancedTable id="xxsuppinvpoprsVO" width="100%" viewName="xxsuppinvpoprVO" tableBanding="rowBanding">
                     <ui:contents>
                        <oa:column id="PrNumberCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="PrNumberHdr" prompt="PR Number"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="PrNumber" viewAttr="PrNumber"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="PrDateCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="PrDateHdr" prompt="PR Date"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="PrDate" viewAttr="PrDate" dataType="DATE"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="RequesterCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="RequesterHdr" prompt="Requester"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="Requester" viewAttr="Requester"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="BuyerCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="BuyerHdr" prompt="Buyer"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="Buyer" viewAttr="Buyer"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="JfNumberCol">
                           <ui:columnHeader>
                              <oa:sortableHeader id="JfNumberHdr" prompt="JF Number"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:messageStyledText id="JfNumber" viewAttr="JfNumber"/>
                           </ui:contents>
                        </oa:column>
                        <oa:column id="PRAttachment" columnDataFormat="objectNameFormat">
                           <ui:columnHeader>
                              <oa:sortableHeader id="sortableHeader22" prompt="PR Attachments"/>
                           </ui:columnHeader>
                           <ui:contents>
                              <oa:attachmentImage id="PRAtt1" searchRegionRendered="false">
                                 <oa:entityMappings>
                                    <oa:entityMap id="entityMap12" insertAllowed="false" updateAllowed="false" deleteAllowed="false" entityId="REQ_HEADERS" showAll="true">
                                       <oa:primaryKeys>
                                          <oa:primaryKey id="primaryKey12" viewAttr="RequisitionHeaderId"/>
                                       </oa:primaryKeys>
                                       <oa:categoryMappings>
                                          <oa:categoryMap id="categoryMap12" categoryName="Vendor"/>
                                          <oa:categoryMap id="categoryMap22" categoryName="Approver"/>
                                          <oa:categoryMap id="categoryMap51" categoryName="Buyer"/>
                                          <oa:categoryMap id="categoryMap52" categoryName="REQ Internal"/>
                                          <oa:categoryMap id="categoryMap53" categoryName="Quote Internal"/>
                                          <oa:categoryMap id="categoryMap54" categoryName="PO Internal"/>
                                          <oa:categoryMap id="categoryMap55" categoryName="Payables"/>
                                          <oa:categoryMap id="categoryMap56" categoryName="MISC"/>
                                          <oa:categoryMap id="categoryMap57" categoryName="RCV Internal"/>
                                          <oa:categoryMap id="categoryMap58" categoryName="Receiver"/>
                                          <oa:categoryMap id="categoryMap59" categoryName="REQ Internal"/>
                                       </oa:categoryMappings>
                                    </oa:entityMap>
                                 </oa:entityMappings>
                              </oa:attachmentImage>
                           </ui:contents>
                        </oa:column>
                     </ui:contents>
                  </oa:advancedTable>
               </ui:contents>
            </oa:hideShowHeader>
         </ui:contents>
      </oa:pageLayout>
   </content>
</page>