Query to find concurrent request status

Query to find concurrent request status


The following query finds the concurrent process status and its related information (such as, completion phase, responsibility used, which user submitted the program, etc.).

In the following example, I used "Autoinvoice Import Program" as Concurrent Program Name. You will need to run the query by changing the program name as per your requirement. You can also uncomment the "FCR.REQUEST_ID" condition (at the bottom of the query) for a specific Request ID.


-------------------------------------------------------------------------------
-- Query to find concurrent request status related information
-------------------------------------------------------------------------------
SELECT fu.user_name                           "User ID",
       frt.responsibility_name                "Responsibility Used",
       fcr.request_id                         "Request ID",
       fcpt.user_concurrent_program_name      "Concurrent Program Name",
       DECODE(fcr.phase_code,
              'C',  'Completed',
              'P',  'Pending',
              'R',  'Running',
              'I',  'Inactive',
              fcr.phase_code)                 "Phase",
       DECODE(fcr.status_code,
              'A',  'Waiting',
              'B',  'Resuming',
              'C',  'Normal',
              'D',  'Cancelled',
              'E',  'Error',
              'F',  'Scheduled',
              'G',  'Warning',
              'H',  'On Hold',
              'I',  'Normal',
              'M',  'No Manager',
              'Q',  'Standby',
              'R',  'Normal',
              'S',  'Suspended',
              'T',  'Terminating',
              'U',  'Disabled',
              'W',  'Paused',
              'X',  'Terminated',
              'Z',  'Waiting',
              fcr.status_code)                "Status",
       fcr.request_date                       "Request Date",
       fcr.requested_start_date               "Request Start Date",
       fcr.hold_flag                          "Hold Flag",
       fcr.printer                            "Printer Name",
       fcr.parent_request_id                  "Parent Request ID"
       -- fcr.number_of_arguments,
       -- fcr.argument_text,
       -- fcr.logfile_name,
       -- fcr.outfile_name
  FROM fnd_user                    fu,
       fnd_responsibility_tl       frt,
       fnd_concurrent_requests     fcr,
       fnd_concurrent_programs_tl  fcpt
 WHERE fu.user_id                 =  fcr.requested_by
   AND fcr.concurrent_program_id  =  fcpt.concurrent_program_id
   AND fcr.responsibility_id      =  frt.responsibility_id
   AND frt.LANGUAGE               =  USERENV('LANG')
   AND fcpt.LANGUAGE              =  USERENV('LANG')
   -- AND fcr.request_id = 7137350  -- <change it>
   AND fcpt.user_concurrent_program_name = 'Autoinvoice Import Program'  -- <change it>
 ORDER BY fcr.request_date DESC;

Query to find Bank information

Query to find Bank information


Bank, Bank Account, and Bank Branches information from R12.


-------------------------------------------------------------------------------
-- Query to find Bank, Bank Account, and Bank Branches information
-------------------------------------------------------------------------------
SELECT cba.bank_account_name            "Bank Account Name",
       cba.bank_account_num             "Bank Account Number",
       cba.multi_currency_allowed_flag  "Multi Currency Flag",
       cba.zero_amount_allowed          "Zero Amount Flag",
       cba.account_classification       "Account Classification",
       bb.bank_name                     "Bank Name",
       bb.bank_branch_type              "Bank Branch Type",
       bb.bank_branch_name              "Bank Branch Name",
       bb.bank_branch_number            "Bank Branch Number",
       bb.eft_swift_code                "Swift Code",
       -- bb.description                   "Description",
       ou.name                          "Operating Unit",
       gcf.concatenated_segments        "GL Code Combination"
  FROM ce_bank_accounts          cba,
       ce_bank_acct_uses_all     bau,
       cefv_bank_branches        bb,
       hr_operating_units        ou,
       gl_code_combinations_kfv  gcf
 WHERE cba.bank_account_id = bau.bank_account_id
   AND cba.bank_branch_id  = bb.bank_branch_id
   AND ou.organization_id  = bau.org_id
   AND cba.asset_code_combination_id = gcf.code_combination_id
   AND (cba.end_date IS NULL OR cba.end_date > TRUNC(SYSDATE))
 ORDER BY TO_NUMBER(cba.bank_account_num);

Query to find Application Short Name of a module

Query to find Application Short Name of a module

The following query lists all the applications related information. This query can be used to find theAPPLICATION_SHORT_NAME of a module (eg. Payables, Receivables, Order Management, etc.) that are often used for downloading FNDLOAD LDT files, adding responsibility to a user and many more.

You can uncomment the FAT.APPLICATION_NAME condition (very bottom line of the query) to learn about a particular module. In this case, I used "Payables".

-------------------------------------------------------------------------------
-- Query to find all APPLICATION (module) information
-------------------------------------------------------------------------------
SELECT fa.application_id           "Application ID",
       fat.application_name        "Application Name",
       fa.application_short_name   "Application Short Name",
       fa.basepath                 "Basepath"
  FROM fnd_application     fa,
       fnd_application_tl  fat
 WHERE fa.application_id = fat.application_id
   AND fat.language      = USERENV('LANG')
   -- AND fat.application_name = 'Payables'  -- <change it>
 ORDER BY fat.application_name;

-

Customer Standard Profile Class

Customer Standard Profile Class


While creating a new customer through the Customer Standard form of Order Management Super User Responsibility, the system threw up an error message "Error: Provide a positive integer for minimum customer balance amount or percent when balance amount overdue type is amount or percent respectively". Below steps can be followed to resolve the error.

1.Login to the application with ‘Oracle Order Management Super User’ Responsibility

2.The Navigation Path is Customers>Profile Class.

3.Query for the Profile Class “DEFAULT”

4.For each currency, make sure that the Minimum Customer Balance is not null and there exists a corresponding value.

5.Save the Changes.

Following screenshots depicts the above steps

Navigation: Customer >Profile Class.

Query the Profile Class “DEFAULT”

How to find OPP log file - XML Publisher
The Concurrent Request ends with Phase 'Completed' and Status 'Warning' which indicates that the Output Post Processor (OPP) failed to generate an output file.






In such cases the request log file shows a generic error message indicating the the post-processing action has failed.

...
+------------- 1) PUBLISH -------------+
Beginning post-processing of request 3181343 on node FINAPPS at 25-OCT-2011 11:41:30.
Post-processing of request 3181343 failed at 25-OCT-2011 11:41:31 with the error message:
One or more post-processing actions failed. Consult the OPP service log for details.
+--------------------------------------+
...

The actual error returned by the XML Publisher Core engine is captured in the OPP log file.
One of the easiest way to obtain the OPP log file is to run the below script from the database by providing request_id.

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
  FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
 WHERE fcpp.processor_id = fcp.concurrent_process_id
   AND fcpp.action_type = 6
   AND fcpp.concurrent_request_id = &request_id;

Output of the script contains logfile location just like below
/u01/app/inst/apps/NZAPPS/logs/appl/conc/log/FNDOPP10981694.txt

Developer/Administrator/DBA has to go to that location and take the OPP logfile

Alternate Method:


Getting OPP Log from the application itself
a. System Administrator > Concurrent > Manager > Administer
b. Search for 'Output Post Processor'
c. Click the 'Processes' button
d. Click the Manager Log button. This will open the 'OPP'
e. Upload the OPP log file.

AP to Bank Details Query

SELECT party_supp.party_name supplier_name
, aps.segment1 supplier_number
, ass.vendor_site_code supplier_site
, ieb.bank_account_num
, ieb.bank_account_name
, party_bank.party_name bank_name
, branch_prof.bank_or_branch_number bank_number
, party_branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
FROM hz_parties party_supp
, ap_suppliers aps
, hz_party_sites site_supp
, ap_supplier_sites_all ass
, iby_external_payees_all iep
, iby_pmt_instr_uses_all ipi
, iby_ext_bank_accounts ieb
, hz_parties party_bank
, hz_parties party_branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
WHERE party_supp.party_id = aps.party_id
AND party_supp.party_id = site_supp.party_id
AND site_supp.party_site_id = ass.party_site_id
AND ass.vendor_id = aps.vendor_id
AND iep.payee_party_id = party_supp.party_id
AND iep.party_site_id = site_supp.party_site_id
AND iep.supplier_site_id = ass.vendor_site_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = party_bank.party_id
AND ieb.bank_id = party_branch.party_id
AND party_branch.party_id = branch_prof.party_id
AND party_bank.party_id = bank_prof.party_id
ORDER BY party_supp.party_name
, ass.vendor_site_code;

Yearly Calendar Query From JAN to DE

SELECT INITCAP(TRIM(TO_CHAR(dat, 'month'))) || ', ' || TO_CHAR(SYSDATE, 'yyyy') MONTH,
MAX(DECODE(TO_CHAR(dat, 'd'), 2, TO_CHAR(dat, 'dd'))) mon,
MAX(DECODE(TO_CHAR(dat, 'd'), 3, TO_CHAR(dat, 'dd'))) tue,
MAX(DECODE(TO_CHAR(dat, 'd'), 4, TO_CHAR(dat, 'dd'))) wed,
MAX(DECODE(TO_CHAR(dat, 'd'), 5, TO_CHAR(dat, 'dd'))) thu,
MAX(DECODE(TO_CHAR(dat, 'd'), 6, TO_CHAR(dat, 'dd'))) fri,
MAX(DECODE(TO_CHAR(dat, 'd'), 7, TO_CHAR(dat, 'dd'))) sat,
MAX(DECODE(TO_CHAR(dat, 'd'), 1, TO_CHAR(dat, 'dd'))) sun
FROM
(SELECT TRUNC(SYSDATE, 'y') + ROWNUM -1 dat,
TO_CHAR(TRUNC(SYSDATE, 'y') + ROWNUM -1, 'iw') woy
FROM user_objects
WHERE ROWNUM <=(ADD_MONTHS(TRUNC(SYSDATE, 'y'), 12) -TRUNC(SYSDATE, 'y')))
GROUP BY TO_CHAR(dat, 'month'),
woy,
TO_CHAR(dat, 'mm')
ORDER BY TO_CHAR(dat, 'mm'),8;