Thursday 8 August 2019

Define and assign Data Access Set in Oracle Apps.

Data Access Set:
          
      Data access set is used to restrict balancing segment values or management segment values with read or write privileges to a particular responsibility or user.
                Once we complete a ledger with the entire ledger options system internally generates a default data access set with the same name as our primary ledger which is going to give access to full ledger with read and write privileges. This will be automatically assigned to the responsibility using GL data access set profile option.   The default data access set will give access to full ledger with read and write privilege.  We cannot modify default data access set.
                We can also define additional data access set based on our business requirement. We can define any number of data access sets but only one data access set can be assigned to general ledger responsibility by using GL data access set profile option at any point of time.
                We cannot perform open/close periods from a responsibility to which we have a restricted data access set. We will be allowed to perform open/close periods only   those responsibilities to which we have full ledger access
Access set types:
1.       Full ledger
2.       Balancing segment value
3.       Management segment value
Full ledger:
                Full ledger accesses means having full read and write access to the ledger and all of its balancing segment values or management segment values
Balancing segment value:
                If you assign specific balancing segment values to legal entities and ledgers, you will only be able to use those balancing segment values during transaction processing and journal entry.
Management segment value:
                When securing management segment values for a ledger, take note of the management segment values you used when you assigned the default accounts to the ledger, such as the retained earnings account and the cumulative translation adjustment account.

The privileges available for all the three Data Access sets are:
Read only:  Users allow viewing data in ledgers and balancing or management segment values.
Read and Write: Allows users to view and enter data in ledgers and balancing or management segment values.

Steps required for defining data access set:
Step1:
                Define data access set
Navigation:
               Go to GL Super User Responsibility => Setup => financials => data access set
1.       Enter data access set name.
2.       Select chart of accounts.
3.       Select calendar.
4.       Select access set type.
5.       Enter access details.
6.       Select ledger/ledger set.
7.       Select specific balancing or management segment value.
8.       Select privileges read and write or read only as per our requirement.
9.       Save your work.
Step2:
                Assign data access set to GL responsibility
Navigation:
                Application: System administrator
                Profile => system

Tuesday 12 February 2019

Query to get India timezone Date & Time.

--Query to get India timezone Date & Time
SELECT TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP) +
       (5.5/24), 'DD/Mon/RRRR HH24:MI:SS') IST_DATE_TIME
  FROM dual;

Script to Enable & Disable ERP Users through API.

--Script to Disable ERP User
BEGIN
FND_USER_PKG.DisableUser(username => 'XYZ_USER');
commit;
END;


----Script to Enable ERP User
BEGIN
FND_USER_PKG.EnableUser(username => 'XYZ_USER');
commit;
END;

Query to Get VENDOR, PO, INVOICE AND PAYMENT DETAILS

--Query to Get VENDOR, PO, INVOICE AND PAYMENT DETAILS
SELECT DISTINCT a.org_id
      ,e.segment1 VENDOR_NUMBER
      ,e.vendor_name
      ,UPPER (e.vendor_type_lookup_code) VENDOR_TYPE
     ,f.vendor_site_code
     ,TO_CHAR (TRUNC(d.creation_date)) PO_DATE
     ,d.segment1 PO_NUM
     ,d.type_lookup_code PO_TYPE
     ,c.quantity_ordered QTY_ORDERED
     ,c.quantity_cancelled QTY_CANCELLED
     ,g.item_description ITEM_DESCRIPTION
     ,g.unit_price UNIT_PRICE
     ,((NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0))
      * NVL (g.unit_price, 0)) PO_LINE_AMOUNT
     ,(SELECT DECODE (ph.approved_flag,'Y', 'Approved')
         FROM po.po_headers_all ph
        WHERE ph.po_header_id = d.po_header_id) PO_APPROVED
     ,a.invoice_type_lookup_code INVOICE_TYPE
     ,a.invoice_amount INVOICE_AMOUNT
     ,TO_CHAR (TRUNC (a.invoice_date)) INVOICE_DATE
     ,a.invoice_num INVOICE_NUMBER
     ,(SELECT DECODE (x.match_status_flag,'A', 'Approved')
         FROM ap.ap_invoice_distributions_all x
        WHERE x.invoice_distribution_id = b.invoice_distribution_id) INVOICE_APPROVED
     ,a.amount_paid
     ,h.check_id
     ,i.check_number
     ,h.invoice_payment_id
     ,TO_CHAR (TRUNC(i.check_date)) PAYMENT_DATE
FROM ap_invoices_all a
    ,ap_invoice_distributions_all b
    ,po_distributions_all c
    ,po_headers_all d
    ,ap_suppliers e
    ,ap_supplier_sites_all f
    ,po_lines_all g
    ,ap_invoice_payments_all h
    ,ap_checks_all i
WHERE a.invoice_id = b.invoice_id
  AND b.po_distribution_id = c.po_distribution_id(+)
  AND c.po_header_id = d.po_header_id(+)
  AND e.vendor_id(+) = d.vendor_id
  AND f.vendor_site_id(+) = d.vendor_site_id
  AND d.po_header_id = g.po_header_id
  AND c.po_line_id = g.po_line_id
  AND a.invoice_id = h.invoice_id
  AND h.check_id = i.check_id
  AND c.po_header_id IS NOT NULL
  AND a.payment_status_flag = 'Y'
  AND d.type_lookup_code != 'BLANKET'
  AND EXISTS (SELECT 1
      FROM hr_operating_units
     WHERE 1=1
       AND organization_id = d.org_id
       AND business_group_id = 81)
ORDER BY E.VENDOR_NAME

Query to Check Period Status for GL/PO/AP/AR/FA Applications

--Query to Check Period Status for GL/PO/AP/AR/FA Applications
SELECT (SELECT sob.NAME
          FROM gl_sets_of_books sob
         WHERE sob.set_of_books_id = a.set_of_books_id) ledger_Name
      ,a.period_name Period_Name
      ,a.period_num Period_Num
      ,a.gl_status GL_Period_Status
      ,b.po_status PO_period_Status
      ,c.ap_status AP_period_Status
      ,d.ar_status AR_period_Status
      ,e.fa_status FA_period_Status
 FROM (SELECT period_name
             ,period_num
             ,DECODE (closing_status,
            'O', 'Open',
            'C', 'Closed',
            'F', 'Future',
            'N', 'Never', closing_status) gl_status
     ,set_of_books_id
 FROM gl_period_statuses
WHERE application_id = 101
  AND UPPER (period_name) = UPPER (:PERIOD_NAME)
  AND set_of_books_id =  :P_SOB_ID) a,
    (SELECT period_name,
            DECODE (closing_status,
            'O', 'Open',
            'C', 'Closed',
            'F', 'Future',
            'N', 'Never',closing_status) po_status
           ,set_of_books_id
    FROM gl_period_statuses
    WHERE application_id = 201
    AND UPPER (period_name) = UPPER (:PERIOD_NAME)
    AND set_of_books_id = :P_SOB_ID ) b,
        (SELECT period_name,
                DECODE (closing_status,
            'O', 'Open',
            'C', 'Closed',
            'F', 'Future',
            'N', 'Never',closing_status) ap_status
           ,set_of_books_id
      FROM gl_period_statuses
     WHERE application_id = 200
       AND UPPER (period_name) = UPPER (:PERIOD_NAME)
       AND set_of_books_id = :P_SOB_ID ) c,
        (SELECT period_name,
         DECODE (closing_status,
            'O', 'Open',
            'C', 'Closed',
            'F', 'Future',
            'N', 'Never',
            closing_status) ar_status
           ,set_of_books_id
      FROM gl_period_statuses
     WHERE application_id = 222
       AND UPPER (period_name) = UPPER (:PERIOD_NAME)
       AND set_of_books_id = :P_SOB_ID) d,
    (SELECT fdp.period_name,
            DECODE (fdp.period_close_date,
            NULL,'Open','Closed') fa_status
          ,fbc.set_of_books_id
    FROM fa_book_controls fbc
        ,fa_deprn_periods fdp
    WHERE fbc.set_of_books_id = :P_SOB_ID
    AND fbc.book_type_code = fdp.book_type_code
    AND UPPER (fdp.period_name) = UPPER (:PERIOD_NAME)) e
WHERE a.period_name = b.period_name(+)
  AND a.period_name = c.period_name(+)
  AND a.period_name = d.period_name(+)
  AND a.period_name = e.period_name(+)
  AND a.set_of_books_id=b.set_of_books_id(+)
  AND a.set_of_books_id=c.set_of_books_id(+)
  AND a.set_of_books_id=d.set_of_books_id(+)
  AND a.set_of_books_id=e.set_of_books_id(+)
ORDER BY 1

Query to get PO created but RECEIPT/GRN not done.

SELECT pha.po_Header_id
      ,pha.segment1 PO_NUMBER
      ,pha.CLOSED_CODE
      ,to_char(pha.CREATION_DATE,'DD-MON-YYYY') CREATION_DATE
      --,To_char(pha.CREATION_DATE,'DD-MON-YYYY HH24:MI:SS') Creation_Date_Time
      ,hou.name OPERATING_UNIT
      ,aps.vendor_id
      ,aps.segment1 vendor_number
      ,aps.vendor_name
  FROM po_Headers_all pha
      ,ap_suppliers aps
      ,hr_operating_units hou
 WHERE 1=1
   --AND pha.vendor_order_num LIKE '%SA%'
   AND pha.vendor_id = aps.vendor_id
   AND hou.business_group_id = 81
   AND pha.AUTHORIZATION_STATUS = 'APPROVED'
   AND pha.org_id = hou.organization_id
   AND cancel_flag <> 'Y'
   AND pha.APPROVED_DATE BETWEEN fnd_date.canonical_to_date (:P_DT_FRM) AND fnd_date.canonical_to_date (:P_DT_TO)
  AND NOT EXISTS
  (SELECT 1
     FROM rcv_shipment_lines rsl
    WHERE PHA.PO_HEADER_ID = RSL.PO_HEADER_ID)
ORDER BY pha.CREATION_DATE DESC;

Query to find request group and application information for a concurrent program

SELECT cpt.user_concurrent_program_name
      ,DECODE(rgu.request_unit_type,
              'P', 'Program',
              'S', 'Set',
              rgu.request_unit_type) request_unit_type
      ,cp.concurrent_program_name Concurrent_Program_Short_Name
      ,rg.application_id
      ,rg.request_group_name
      ,fat.application_name
      ,fa.application_short_name
      ,fa.basepath application_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 = :p_user_program_name OR :p_user_program_name IS NULL)

Query to get BI/XML Publisher Data Definition and Template details

--Query to get XML Publisher Data Definition and Template details
SELECT fcp.user_concurrent_program_name "Program Name",
         fcp1.concurrent_program_name "Program Short Name",
         fat.application_name program_application_name,
         fet.executable_name "Executable Name",
         fat1.application_name executable_application_name,
         fet.execution_file_name,
         flv.meaning execution_method,
         xlo.lob_type,
         xlo.file_name "Data/Brusting File",
         xds.data_source_code,
         xds.data_source_name,
         xdt.template_code,
         xlt.language,
         xlt.file_name
    FROM fnd_concurrent_programs_tl fcp,
         fnd_concurrent_programs fcp1,
         fnd_executables fet,
         fnd_lookup_values flv,
         fnd_application_tl fat,
         fnd_application_tl fat1,
         xdo_lobs xlo,
         xdo_ds_definitions_vl xds,
         xdo_templates_b xdt,
         xdo_templates_tl xdtl,
         xdo_lobs xlt
   WHERE 1=1   
     AND (fcp.application_id = :p_app_id OR :p_app_id IS NULL)
     AND fcp.language = 'US'
     AND fcp.concurrent_program_id = fcp1.concurrent_program_id
     AND fcp.application_id = fcp1.application_id
     AND fcp1.executable_id = fet.executable_id
     AND fcp1.executable_application_id = fet.application_id
     AND flv.lookup_code = fet.execution_method_code
     AND flv.language = 'US'
     AND FLV.LOOKUP_TYPE = 'CP_EXECUTION_METHOD_CODE'
     AND fcp1.application_id = fat.application_id
     AND fet.application_id = fat1.application_id
     AND fat.language = 'US'
     AND fat1.language = 'US'
     AND fet.executable_name = 'XDODTEXE'
     AND fcp1.concurrent_program_name = xlo.lob_code(+)
     AND xlo.lob_type IN ('DATA_TEMPLATE', 'BURSTING_FILE')
     AND (fcp1.concurrent_program_name = :p_program_code OR :p_program_code IS NULL)
     AND fcp1.concurrent_program_name = xds.data_source_code
     AND xds.data_source_code = xdt.data_source_code(+)
     AND xdt.template_code = xdtl.template_code(+)
     AND xdtl.language = 'US'
     AND xds.data_source_name = xdtl.template_name
     AND xlt.lob_type = 'TEMPLATE_SOURCE'
     AND xdt.template_code = xlt.lob_code
ORDER BY 1;

Wednesday 23 January 2019

Query to Find Scheduled Concurrent Programs

SELECT DISTINCT
     fcr.request_id
     ,fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) Concurrent_program_name
     ,fu.user_name requestor
     ,fu.description requested_by
     ,fu.email_address
     ,frt.responsibility_name requested_by_resp
     ,(SELECT meaning
         FROM fnd_lookups
        WHERE lookup_type= 'CP_PHASE_CODE'
          AND lookup_code =fcr.phase_code) program_Phase
     ,(SELECT meaning
         FROM fnd_lookups
        WHERE lookup_type= 'CP_STATUS_CODE'
        AND lookup_code =fcr.status_code) program_Status
     ,fcr.argument_text program_parameters
      , TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
     ,TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start  
     ,TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
     ,DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
     ,CASE WHEN fcr.hold_flag = 'Y' THEN
          Substr( fu.description, 0 , 40)
      END last_update_by
     ,CASE WHEN fcr.hold_flag = 'Y' THEN
           fcr.last_update_date
       END last_update_date
     ,fcr.increment_dates
     ,CASE WHEN fcrc.CLASS_INFO IS NULL THEN
        'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
       ELSE 'N/A'
       END RUN_ONCE
     ,CASE WHEN fcrc.class_type = 'P' THEN
        'Repeat every ' ||
        substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
        decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
               'N', ' minutes',
               'M', ' months',
               'H', ' hours',
               'D', ' days') ||
        decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
               'S', ' from the start of the prior run',
               'C', ' from the completion of the prior run')
       ELSE 'N/A'
       END set_days_of_week
     ,fcr.resubmit_interval||' '||fcr.resubmit_interval_unit_code Resubmit_Interval
     ,NVL2(fcr.resubmit_interval,'PERIODICALLY',NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS','ONCE')) schedule_type
  FROM fnd_concurrent_requests fcr
      ,fnd_user fu
      ,fnd_concurrent_programs fcp
      ,fnd_concurrent_programs_tl fcpt
      ,fnd_printer_styles_tl fpst
      ,fnd_conc_release_classes fcrc
      ,fnd_responsibility_tl frt
      ,fnd_lookups fl
 WHERE 1=1
   AND fcp.application_id = fcpt.application_id
   AND fcr.requested_by = fu.user_id
   AND fcr.concurrent_program_id = fcp.concurrent_program_id
   AND fcr.program_application_id = fcp.application_id
   AND fcr.concurrent_program_id = fcpt.concurrent_program_id
   AND fcr.responsibility_id = frt.responsibility_id
   AND fcr.print_style = fpst.printer_style_name(+)
   AND fcr.release_class_id = fcrc.release_class_id(+)
   AND fcr.status_code = fl.lookup_code
   AND fl.lookup_type = 'CP_STATUS_CODE'
   AND (fcpt.user_concurrent_program_name = :P_PROGRAM_NAME OR :P_PROGRAM_NAME IS NULL)
Order By Fu.Description;

Thursday 17 January 2019

Query to list all objects of responsibility ie. submenu, menu and function

-- Query to list all objects of responsibility--
SELECT lvl r_lvl,
         rownumber rw_num,
         entry_sequence seq,
         (lvl || '.' || rownumber || '.' || entry_sequence) menu_seq,
         menu_name,
         sub_menu_name,
         prompt,
         fm.description,
         TYPE,
         function_name,
         user_function_name,
         fff.description form_description
    FROM (    SELECT LEVEL lvl,
                     ROW_NUMBER ()
                     OVER (PARTITION BY LEVEL, menu_id, entry_sequence
                           ORDER BY entry_sequence)
                        AS rownumber,
                     entry_sequence,
                     (SELECT user_menu_name
                        FROM fnd_menus_vl fmvl
                       WHERE 1 = 1 AND fmvl.menu_id = fmv.menu_id)
                        menu_name,
                     (SELECT user_menu_name
                        FROM fnd_menus_vl fmvl
                       WHERE 1 = 1 AND fmvl.menu_id = fmv.sub_menu_id)
                        sub_menu_name,
                     function_id,
                     prompt,
                     description
                FROM apps.fnd_menu_entries_vl fmv
          START WITH menu_id =
                        (SELECT menu_id
                           FROM apps.fnd_responsibility_vl
                          WHERE UPPER (responsibility_name) =
                                   UPPER (:resp_name))
          CONNECT BY PRIOR sub_menu_id = menu_id) fm,
         apps.fnd_form_functions_vl fff
   WHERE fff.function_id(+) = fm.function_id
ORDER BY lvl, entry_sequence;
/

NOTE:
1. Please pass the responsibility in parameter.
2. It'll show all submenu and function level wise.
3. R_lvl is a sequence number which starts with Parent Menu Level and drills down to child menu
4. ROW_NUM is sequence number for sub menus.
5. Seq is a sequence number as per the Oracle Apps.

Tuesday 8 January 2019

Namespace prefix 'ref' used but not declared. Error in BI Publisher Report.

Below error comes when we used higher version of BI Publisher and reports will be completing with warning.

[1/6/19 1:57:03 AM] [798109:RT11983076] Output file was found but is zero sized - Deleted
[1/6/19 1:57:03 AM] [UNEXPECTED] [798109:RT11983076] java.lang.reflect.InvocationTargetException
Caused by: org.xml.sax.SAXException: Namespace prefix 'ref' used but not declared.
oracle.xdo.parser.v2.XPathException: Namespace prefix 'ref' used but not declared.
        at oracle.xdo.parser.v2.XSLProcessor.reportException(XSLProcessor.java:806)
        at oracle.xdo.parser.v2.XSLProcessor.newXSLStylesheet(XSLProcessor.java:614)


In case MS Word (rtf template) Properties of the XML Field has source something like ‘<?ref:xdo0002?>’ then you will face this error.


Solution:
To resolve this issue we need to set "form field size" option as "Backward Compatible". See as follows.


Navigation: Open report Template => BI Publisher Tab => Option (highlighted as yellow) => Window will open => Select "Build" => Select "Backward Compatible" from "form field size" section.





Save the changes and upload the changed template and retest.