Jobs with Approval Assignments

/* Formatted on 2016/02/24 11:37 (Formatter Plus v4.8.0) */
SELECT   hou.NAME op_unit,
         DECODE (pj.business_group_id,
                 81, 'US',
                 141, 'DK',
                 142, 'DE',
                 pj.business_group_id
                ) business_group,
         pj.job_id, pjt.NAME job_name, pcga.control_group_name,
         pcf.control_function_name, pcf.description, pcf.document_type_code,
         pcf.document_subtype, ppca.*                     ---ppca.* --- pcga.*
    FROM apps.po_position_controls_all ppca,
         apps.per_jobs_tl pjt,
         apps.po_control_groups_all pcga,
         apps.po_control_functions pcf,
         apps.hr_all_organization_units hou,
         apps.per_jobs pj
   WHERE ppca.job_id = pjt.job_id                                  ---1=1 ----
     AND pjt.job_id = pj.job_id
     AND ppca.control_group_id = pcga.control_group_id
     AND ppca.org_id = pcga.org_id
     AND ppca.control_function_id = pcf.control_function_id
     AND ppca.org_id = hou.organization_id
---and ppca.org_id = 202 and pj.business_group_id <> 142
     AND ppca.org_id = 202                 --- and pj.business_group_id <> 141
---and pjt.name = 'Senior Director.Executive/Senior-Level Officials and Managers' ---'Coordinator.Administrative Support Workers'
---and ppca.creation_date > trunc(sysdate)
     AND pjt.LANGUAGE = 'US'
     AND document_subtype IN ('INTERNAL', 'PURCHASE', 'STANDARD')
     AND pcf.control_function_name = 'Approve Purchase Requisitions'
---and pcf.control_function_name = 'Approve Standard Purchase Orders'
---and pjt.name = 'Specialist.Professionals'
---and pjt.name = 'Analyst.Professionals'
---order by hou.name, pjt.name, pcf.document_type_code, pcf.document_subtype
     AND pjt.NAME LIKE
                'Vice President.Executive/Senior-Level Officials and Managers'
ORDER BY hou.NAME,
         pcf.control_function_name,
         pjt.NAME,
         pcf.document_type_code,
         pcf.document_subtype;

Create Accounting - Cost Management with Error 95325 The GL Date DD-MON-YYYY is not in an Open or a Future Enterable Period. Please select a valid Period

Note: 
If reopening a closed periods not feasible, then review the following Action Plan:

1. Events created in closed GL period can be identified using below query:   

   SELECT  xe.* 
     FROM xla_ae_headers xah, 
          xla_events xe, 
          xla_ae_lines xal, 
          xla_accounting_errors xae, 
          gl_code_combinations_kfv gcc 
 WHERE xae.application_id=707 
   AND xe.application_id=707 
   AND xal.application_id=707 
   AND xah.application_id=707 
   AND xae.ledger_id=&ledger_id 
   AND xae.ae_header_id=xah.ae_header_Id 
   AND xah.ae_header_Id=xal.ae_header_id 
   AND xae.event_id=xe.event_id 
   AND xe.event_id=xah.event_id 
   AND xah.GL_TRANSFER_STATUS_CODE='N' 
   AND xal.code_combination_id=gcc.code_combination_id 

2. A Note of Caution : It is to be understood that sweeping of transactions with the above approach would not change the transaction date in Material/WIP subledgers and hence there could be discrepancy between these periods in the Materials Subledger Vs SLA/GL.
    It could also impact drilldown for these transactions.

3. Please use sweep datafix from Note 883557.1 for updating records whose xah.accounting_entry_status_code='F'. 

    Use the following for pending transactions have xah.accounting_entry_status_code='I'

4. The following is to transfer (Sweep) these pending to current period.

    a. Take back up of the relevant events that are in error with message 95325
     This will only fix events that have errored due to corresponding period being closed.  Customer could choose to add other filtering criteria to narrow down further, if required.

       CREATE TABLE XE_Sweep_bkup_orcl AS
             SELECT xe.*
                     FROM xla_events xe,  xla_accounting_errors xae
                    WHERE xe.application_id = 707
              AND xae.application_id = 707
                    --  AND ENTITY_CODE  =
              AND xe.event_id = xae.event_id
              AND xe.upg_batch_id IS NULL
              AND xae.message_number = 95325
                      AND xae.ledger_id = &Ledger_id
                      AND xe.event_status_code =  'U'
                      AND xe.process_status_code = 'I'

  b. Verify the events from the backup table to ensure they are the ones that would like to sweep.

  c. Sweep them to next period by providing appropriate date in the next open period 

     Update xla_events
     SET EVENT_DATE = '&New_date',
      transaction_date = '&New_date',
      REFERENCE_DATE_1  =  '&New_date',
      CREATED_BY = -11111
     WHERE application_id = 707
     AND Event_id IN (SELECT event_id FROM XE_Sweep_bkup_orcl);

   d. Verify the Numbers of records updated. 
                                                   
   e. COMMIT

   f. Rerun Create Accounting - Cost Management and verify that these transactions get accounted successfully.

Note
-----
If the SLA events already in Final mode  (accounting_entry_status_code='F').
Then also use the below data fix  in xla_ae_headers table :

The following is an example:

update xla_ae_headers xah
 set xah.accounting_date = to_date('01.01.2016', 'dd.mm.yyyy'),
     xah.period_name     = '???-16'  ----its JAN-16
where xah.application_id = 707
 and xah.event_type_code = 'RECEIVE'
 and xah.event_id in (300586735, 300586736, 300586739, 300586740)
 and xah.ledger_id = &ledger_id
 and xah.gl_transfer_status_code = 'N';

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

   EVENT_STATUS_CODE MEANINGS: 
              I - Incomplete, N - No action, P - Processed, U – Unprocessed

    PROCESS_STATUS_CODE MEANINGS: 
              D - Draft, E - Error, I - Invalid, P - Processed, R - Related event in error, U - Unprocessed, F - Final

Paymentech Credit Card Testing in Test Instance

Paymentech Credit Card Testing in Test Instance

1)Testing HTTP (non SSL) connections from database to ECAPP servlet run the following query:

 SQL>select utl_http.request('&value_of_IBY_ECAPP_URL') from dual;

Must return:

Results

OapfCode: IBY_204704
OapfStatus: 3
...

NOTE: Please also verify the value of profile option "IBY: HTTP Proxy" when executing this test.

2)If you are using  HTTPS connection(SSL) please follow below query

Run the script bellow in SQL*Plus. The script will require the values set for the two profile options required at this code level.

SQL>variable wallet_loc varchar2(300);

SQL>exec begin :wallet_loc := iby_netutils_pvt.path_to_url('&value_of_FND_DB_WALLET_DIR'); end;

SQL>select utl_http.request('&value_of_IBY_ECAPP_URL', null, :wallet_loc, null) from dual;

Must return:

Results

OapfCode: IBY_204704
OapfStatus: 3
...

If you get any error in the sql result please follow the below note and check for the error.

Troubleshooting iPayment/Payments connection from Database to Engine (Doc ID 313951.1).

Follow below note if you are using Encryption in PROD and cloned the PROD data in TEST instance

How To Reset Oracle Payments Encryption Wallet Location After Instance Clone ? (Doc ID 1571608.1)

If you still face the same error then enable the debug as per below note and verify the log for the cause.

If Issue still exits please open oracle SR 
Follow the section "A" of the below note and upload all the files to the SR mentioned in the step 7.

Debug logs How to generate Debug Log Files for Oracle Payments Funds Capture R12 (Doc ID 452830.1).