Applies to:

Oracle Receivables - Version 12 and later
Information in this document applies to any platform.
FND_REQUEST.SUBMIT_REQUEST
ARLPLB

Purpose

This sample code will demonstrate the API: FND_REQUEST.SUBMIT_REQUEST to call Process Lockboxes program (ARLPLB) in Release 12.

Requirements

SQL*Plus or Oracle SQL Developer to execute the script

Configuring

The Lockbox process takes the below parameters, text in parenthese are the field names within arlplb.opc:

  1. new transmission (Y/N) (NEW_TRANSMISSION)
  2. transmission_id (LB_TRANSMISSION_ID)
  3. original request id (ORIG_REQUEST_ID)
  4. transmission name (TRANSMISSION_NAME)
  5. submit import (Y/N) (SUBMIT_IMPORT)
  6. datafile (DATA_FILE)
  7. control file (CNTRL_FILE)
  8. transmission format id (TRANSMISSION_FORMAT_ID)
  9. submit validation (Y/N) (SUBMIT_VALIDATION)
  10. pay unrelated invoices (Y/N) (PAY_UNRELATED_INVOICES)
  11. lockbox ID (LOCKBOX_ID)
  12. gl date (GL_DATE)
  13. report format (REPORT_FORMAT)
  14. complete batches only (Y/N) (COMPLETE_BATCHES_ONLY)
  15. submit postbatch (Y/N) (SUBMIT_POSTBATCH)
  16. alternate name search option (ALTERNATE_NAME_SEARCH)
  17. post partial amount or reject entire receipt (Y/N) (IGNORE_INVALID_TXN_NUM)
  18. USSGL transaction code (USSGL_TRN_CODE)
  19. Organization Id (ORG_ID)
  20. Apply Unearned Discount (Y/N) (APPLY_UNEARN_DISC)
  21. Number of Instances (1-99) (NO_OF_INSTANCES)
  22. Source type flag (S - SmartCash or L - Lockbox) (SOURCE_TYPE_FLAG)
  23. scoring model Id (SCORING_MODEL_ID)

Considering the definition of the concurrent program is volatile and may change over time, you can always check for the parameters as follows:

Responsibility: System Administrator
Navigation: Concurrent > Program > Define

Run a search on Short Name = ARLPLB and scan through the list of parameters

Please ensure to modify the input parameters accordingly and send either "Y" or "N".

Note:
1) Apply Unearn discounts parameter has been introduced as part of Bug 9288902 SETUP OPTION NOT TO CALCULATE UNEARNED DISC FOR APPLICATIONS VIA LOCKBOX.
2) The parameter for "Source type flag" should be passed in as "L", otherwise, you might find following error:

ARLPLB module: Process Lockboxes

Creating new row in ar_batches
Inserted batch and updated last_batch_num
Submission is Smartcash, bypassing insert to Interim tables.
No more receipts for this batch
No more batches for this lockbox

Instructions

1. You must run this code in the APPS schema.
2. Your options to run the code are:
You can save the code in a PL/SQL package and register the procedure as a concurrent program to run from within the application
You can run it stand alone directly from PL/SQL.

Caution

This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Sample Code

To set the proper context when running the process through a script, you need to determine the value for USER_ID and RESPONSIBILITY_ID. This provides information to the process so that it can simulate running as if you were logged into the Oracle Receivables application.

SELECT USER_ID,RESPONSIBILITY_ID,
RESPONSIBILITY_APPLICATION_ID, SECURITY_GROUP_ID
FROM FND_USER_RESP_GROUPS
WHERE USER_ID = (SELECT USER_ID
FROM FND_USER
WHERE USER_NAME = '&user_name')
AND RESPONSIBILITY_ID = (SELECT RESPONSIBILITY_ID
FROM FND_RESPONSIBILITY_VL
WHERE RESPONSIBILITY_NAME = '&resp_name');


You can also retrieve the above values using Help > Diagnostics > Examine menu from the application.
Block = $PROFILE$
Field = RESP_ID and USER_ID (APPL_ID is always 222)

SET SERVEROUTPUT ON;

DECLARE
v_return_val Number;

BEGIN
--Values shown here need to be replace with actual value

--fnd_global.apps_initialize(&user_id, &resp_id, &appl_id);
fnd_global.apps_initialize(1318, 50559, 222);

--mo_global.set_policy_context('S',&org_id);
mo_global.set_policy_context('S',204);

--fnd_request.set_org_id(&org_id);
fnd_request.set_org_id(204);

v_return_val := fnd_request.submit_request
('AR',    -- Application short name
'ARLPLB', -- program short name
NULL,     -- program name
NULL,     -- start date
FALSE,    -- sub-request
-- the following are ARLPLB parameters:
'Y',      --1. new transmission
NULL ,    --2. transmission_id
NULL ,    --3. original request id
'ptotatest10',  --4. transmission name
'Y',            --5. submit import
'/test.dat', --6. datafile
'ardeft', --7. control file
100,      --8. transmission format id
'N',      --9. submit validation
NULL ,    --10. pay unrelated invoices
NULL ,    --11. lockbox ID
NULL ,    --12. gl date
NULL ,    --13. report format
'Y',      --14. complete batches only
'N',      --15. submit postbatch
'N',      --16. alternate name search option
'N',      --17. post partial amount or reject entire receipt
NULL ,    --18. USSGL transaction code
204,      --19. Organization Id
'Y'       --20. apply unearn discounts
1,        --21. Number of instances
'L',      --22. Source Type Flag
1        --23. scoring model)
);
dbms_output.put_line('Concurrent Request ID :' || v_return_val);
COMMIT;
END;

文章標籤

Somebaby 發表在 痞客邦 留言(0) 人氣()

Applies to:

Oracle Payables - Version 12.2.12 and later
Oracle Enterprise Command Center Framework - Version 12.2.12 to 12.2.12 [Release 12.2]
Information in this document applies to any platform.

Goal

How to Grant Privileges to access Enterprise Command Center (ECC)?

Solution

User will need to log into Instance using SYSADMIN User.

Navigation:

1. User Management Responsibility -- > Roles & Role Inheritance Page

2.  Query Code UMX%AP%ECC%

3. Go

4. Choose "Quick Select"

5. Expand and confirm "Add Node" is active

6. Payables Command Center Access Role should have Node added.

7. Once done, clear the cache and re test.

文章標籤

Somebaby 發表在 痞客邦 留言(0) 人氣()

In this Document

  Goal
  Solution
  Table FND_LOBS
  Table FND_DOCUMENTS
  Table FND_ATTACHED_DOCUMENTS
  Concurrent program "Purge Obsolete Generic File Manager Data" - FNDGFMPR
  More documentation
  References

 

Applies to:

Oracle Payables - Version 12.2.10 and later
Information in this document applies to any platform.

Goal

How to purge attachments in with no expiration date to reduce the size of the FND_LOBS LOBSEGMENT?

Per MOS document How To Purge FND_LOBS "export" Records With No EXPIRATION_DATE, ran the program "Purge Obsolete Generic File Manager Data" in the following scenarios:

  • With parameters Expired => No & Program Name => export.  It completed file and purged records from FND_LOBS for Program "export" and program "FND_HELP"
  • But, with parameters Expired => No & Program Name => FNDATTCH , is NOT deleting records from FND_LOBS for program "FNDATTCH".

Even after the executions described, the FND_LOBS table still storing hundreds of gigabytes of space and the LOBSEGMENT linked to table FND_LOB occupies hundreads of gigabytes.  The following SQL from MOS document FAQ - Performance Considerations For FND_LOBS shows this:

SELECT program_name
     , COUNT(0)
     , ROUND( SUM (( DBMS_LOB.getlength(file_data)-1)/1024/1024/1024),1 ) size_gb
  FROM FND_LOBS
 GROUP BY program_name
 ORDER BY size_gb DESC ;
PROGRAM_NAME    COUNT(0)   SIZE_GB
--------------- -------- ---------
FNDATTCH          865331     416.7
                  175993      18.8
SELECT SUM(bytes)/1024/1024/1024
     , s.segment_name, s.segment_type
  FROM DBA_LOBS l
     , DBA_SEGMENTS s
 WHERE s.segment_type = 'LOBSEGMENT'
   AND l.table_name = 'FND_LOBS'
   AND s.segment_name = l.segment_name
 GROUP BY s.segment_name,s.segment_type ;
OWNER    OBJECT_NAME                OBJECT_TYPE CREATED    TABLESPACE_NAME  SUM(B.BYTES/1024/1024/1024)
-------- -------------------------- ----------- ---------- --------------- ----------------------------
APPLSYS  SYS_LOB0000034032C00004$$  LOB         14-MAY-00  APPS_TS_MEDIA                     674.401855

 

Solution

Table FND_LOBS

FND_LOBS stores information about all LOBs managed by the Generic File Manager (GFM). Each row includes the file identifier, name, content-type, and actual data.
Each row also includes the dates the file was uploaded and will expire, the associated program name and tag, and the language and Oracle characterset.

The file data, which is a binary LOB, is stored exactly as it is uploaded from a client browser, which means that no translation work is required during a download to
make it HTTP compliant. Therefore uploads from non-browser sources will have to prepare the contents appropriately (for instance, separating lines with CRLF).

The program_name and program_tag may be used by clients of the GFM for any purpose, such as striping, partitioning, or purging the table if the program is de-installed.
They are otherwise strictly informative. These columns and the expiration date are properly set when the procedure FND_GFM.CONFIRM_UPLOAD is called.
If not called, the column expiration_date remains set, and will eventually be purged by the procedure FND_GFM.PURGE_EXPIRED.

Table Structure:

  Name                   Null?    Type
  ---------------------- -------- ----------------
  FILE_ID                NOT NULL NUMBER
  FILE_NAME                       VARCHAR2(256)
  FILE_CONTENT_TYPE      NOT NULL VARCHAR2(256)
  FILE_DATA                       BLOB
  UPLOAD_DATE                     DATE
  EXPIRATION_DATE                 DATE
  PROGRAM_NAME                    VARCHAR2(32)
  PROGRAM_TAG                     VARCHAR2(32)
  LANGUAGE                        VARCHAR2(4)
  ORACLE_CHARSET                  VARCHAR2(30)
  FILE_FORMAT            NOT NULL VARCHAR2(10)

Many DISTINCT values in column PROGRAM_NAME, FNDATTCH is among them:

  PROGRAM_NAME
  --------------------------------
  BIAF_FR_XML
  HRMS_ADI
  FNDATTCH
  FOLDER_VERIFY
  export
  IBCCITEM - LOAD SEED ROW
  GCS
  PERWSIMG
  IBE
  FND_HELP
  Oracle E Records
  PAY_166_12_ar_KW.pdf
  PER_WS1_gb_UK.pdf
  PER_WS4_gb_UK.pdf
  ...
  ...
  PAY_R167_ar_KW.rtf
  PER_VIS_ar_AE.pdf
  PER_PASS_ar_AE.pdf
  ...
  ...
  PAY_MCP_ar_AE.rtf
  PERDTUPR.rtf
  PAY_NL_IZA_NLTMP.rtf

  68 rows selected.

 

Table FND_DOCUMENTS

This table stores language-independent information about a document. For example, each row contains a document identifier, a category identifier, the method of security used for the document (SECURITY_TYPE, where 1=Organization, 2=Set of Books, 3=Business unit, 4=None), the period in which the document is active, and a flag to indicate whether or not the document can be shared outside of the security type (PUBLISH_FLAG).

Other specifications in this table include: datatype (DATATYPE_ID, where 1=short text, 2=long text, 3=image, 4=OLE object, 5=Web Page, 6=File, etc)

The document can be referenced by many application entities and changed only in the define document form (USAGE_TYPE=S); it can be used as a fill-in-the-blanks document, where each time you use a template, you make a copy of it (USAGE_TYPE=T); or it can be used only one time (USAGE_TYPE=O). Images and OLE Objects cannot be used as templates.

These documents are the ones linked to an EBS entity/transaction, like for example, a Payables Invoice or Payment, or a Purchasing Purchase Order, etc.

FND_LOBS links to FND_DOCUMENTS via the MEDIA_ID column (to match FND_LOBS FILE_ID).

 

Table FND_ATTACHED_DOCUMENTS

This table stores information relating a document to an application entity. For example, a record may link a document to a sales order or an item. Each row contains foreign keys to FND_DOCUMENTS and FND_DOCUMENT_ENTITIES.  FND_DOCUMENT_ENTITIES lists each entity to which attachments can be linked. For example, attachments can be linked to Items, Sales Orders, Invoices, etc.

Per above, the following SQL should show the list of entities related to Payables (AP):

SELECT entity_name, table_name
FROM FND_Document_Entities
WHERE application_Id = 200 /* Oracle Payables */ ;
ENTITY_NAME                TABLE_NAME
-------------------------- ------------------------------
AP_CHECKS                  AP_CHECKS
AP_INVOICES                AP_INVOICES
                           PO_VENDOR_SITES
                           AP_EXPENSE_REPORT_HEADERS
                           AP_INVOICES_INTERFACE
                           AP_RECURRING_PAYMENTS
                           AP_INVOICES
PO_VENDORS                 PO_VENDORS
8 rows selected.

This shows how all documents of entity_name AP_INVOICES, refer to attachments to Invoices, Expense Reports, Recurring Payments, etc.

 

Concurrent program "Purge Obsolete Generic File Manager Data" - FNDGFMPR

Concurrent parameters are:

  • Expired: Yes/No.
  • Program Name:  The distinct program names stored in table FND_LOBS.  POssible values are: FNDATTCH, FND_HELP, PERWSIMGm, etc.
  • Program Tag:  Also, the distinct progam tags sotred in table FND_LOBS for a given "Program Name".
  • Purge Orphaned:  Yes/No.

Concurrent Executable is the PLSQL call to Server-side API FND_GFM.Purge.  Package FND_GFM source files are:

  • Package Body - $FND_TOP/patch/115/sql/AFGFMB.pls
  • Package Specification - $FND_TOP/patch/115/sql//AFGFMS.pls

When the concurrent program FNDGFMPR executes, API FND.GFM.Purge calls procedure fnd_gfm.purge_expired or procedure fnd_gfm.purge_set as follow:

  • IF concurrent program is called with program name FNDATTCH and Expired parameter set to anything other than "No" (N)
    THEN it calls fnd_gfm.purge_expired ( 'FNDATTCH', [Purge Orphanded] ); 
  • ELSE it calls fnd_gfm.purge_set ( 'FNDATTCH', [Program Tag] );

 

So to the following to reduce the FND_LOBS LOBSEGMENT size:

 

  1. Run concurrent "Purge Obsolete Generic File Manager Data" executed with parameters:
    Program Name = FNDATTCH,  Expired = Yes, and Purge Orphaned = Yes
  2. To confirm the effect of this, run the following SQL:
    SELECT count(*)
      FROM FND_LOBS FL 
     WHERE NOT EXISTS (SELECT '1'  FROM FND_DOCUMENTS FD  WHERE FD.MEDIA_ID = FL.FILE_ID  AND FD.DATATYPE_ID = 6)
       AND PROGRAM_NAME = 'FNDATTCH'
       AND EXPIRATION_DATE IS NULL ;
    For the particular case that originated this MOS document, this step removed 3175 records.
     
    IMPORTANT:  The records removed by the program are records that have NO link or reference to a particular Application Document (Table FND_DOCUMENTS). Hence, the "NOT EXIST" condition in the SQL.
  3. Now, run the following SQL to check for records in FND_LOBS linked to a FND_DOCUMENT.  Notice we removed the "NOT" from the "NOT EXISTS" statement:  
    SELECT count(*) 
      FROM FND_LOBS FL 
     WHERE EXISTS (SELECT '1'  FROM FND_DOCUMENTS FD  WHERE FD.MEDIA_ID = FL.FILE_ID  AND FD.DATATYPE_ID = 6) 
       AND PROGRAM_NAME = 'FNDATTCH'  AND EXPIRATION_DATE IS NULL ;  
    For the particular case that originated this MOS document, this SQL returned almost 900K records.  This is 900K records in FND_LOBS linked to real EBS Applications Documents Files (DataType= 6) that belong to an EBS application module - transaction/entity, like a Payables Invoice or a Purchasing PO.
  4. Check on these transactions "Upload Dates" (since the expiration date is NULL), using: 
    SELECT COUNT(*), TO_CHAR(upload_date, 'YYYY') upload_Year
      FROM FND_LOBS
     WHERE PROGRAM_NAME = 'FNDATTCH'
       AND expiration_date is NULL
     GROUP BY TO_CHAR(upload_date, 'YYYY')
     ORDER BY 2 ;
    For the particular case that originated this MOS document, this SQL returned the following:
     
         COUNT(*) UPLOAD_YEAR
    ---------- -----------
         58699 2015
         80043 2016
         93663 2017
         94081 2018
        118106 2019
        118335 2020
        120961 2021
        102911 2022
        110001 2023
          9846 2024
    10 rows selected.
     Notice there is 10 years of transactions attachments.
  5. Perhaps they need to review their Transaction Retention Policies.

    For transaction related attachments, there is are Purge programs. For example for AP, there is the "Submit Purge" form.  This is a form you can find under the responsibility "Payables Purge".  This form executable file is APXPGSUB.  In this form, the user creates a "Purge" definition that includes a Name, a category which my varies of what needds to be purged; like: Single Invoices, Invoices and POs, Suppliers, etc.  The user also must give the "Last Activity Date" which is the date used to fetch the transactions to purge.  The basic idea is that the Purge will fetch all transactions for which Last Update Date on any of its linked records (Accounting, payment, POs , etc) is before the parameter date entered.

     
    The records purged by the module -specific purge program, include attachments.

    How to run the Payables Purge program:
    1. Login Oracle Applications using a Payables Purge responsibility.
    2. Open the "Submit Purge" form
      (N) Purge 
    3. Create a new "Purge Definition" in the "Submit Purge" form.
       
    4. Once you save the new Purge definition record, you get the button to [Initiate].
      This button will submit the concurrent program "AP/PO Purge Initiation (Selection) Routine" - APXPGSEL
      This program will fetch the records to be purge and create a "preliminary" report for the user to review and decide if they want to proceed with the purge.
      The "Purge definition" will refresh on the screen and now you see the buttons to [Abort], [Restart], or [Confirm]
      Each button has its own concurrent program.  In particular, [Confirm] will run the "AP/PO Purge Deletion Routine" to perform the actual DELETEs from the tables, like AP_INVOICES, AP_INVOICE_LINES, AP_CHECKS, PO_HEADERS, etc depending on the conditions and category set.
      The Purge Deletion routine spawns child process that run in parallel (for better performance).

       
      For the particular case that originated this MOS document, a Payable Purge was submitted with a "Last Activity Date" of 2015.  This purged 377 Invoices (with Lines and distributions, etc), 105 checks, and 203 POs, among other records.  Some attachments were purged as well.
         
  6. Recheck the table FND_LOBS and the LOBSEGMENT using the SQLs given above.
    For the particular case that originated this MOS document, the table and segement size started to decrease after many additional executions of the Payable Purge.
  7. The following SQL should show the count "Totals" of records in FND_LOBS linked to any Payables transaction:
     
    • Totals for Payables: 
      SELECT COUNT(*), TO_CHAR( FLS.upload_date, 'YYYY' ) Upload_Yr, FDE.entity_name, FDE.table_name, FDS.datatype_ID
      FROM FND_ATTACHED_DOCUMENTS FAD
      , FND_DOCUMENTS FDS
      , FND_LOBS FLS
      , FND_DOCUMENT_ENTITIES FDE
      WHERE 2=2
      AND FLS.file_ID = FDS.media_Id
      AND FAD.document_ID = FDS.document_ID
      -- AND FDS.datatype_ID = 6
      AND FLS.program_name = 'FNDATTCH'
      AND FAD.entity_name = FDE.entity_name
      AND FDE.application_ID = 200 -- >> ONLY PAYABLES
      GROUP BY TO_CHAR( FLS.upload_date, 'YYYY' ), FDE.entity_name, FDE.table_name, FDS.datatype_ID
      ORDER BY 2, 3, 4, 5 ;
       
    • For details on the transactions: 
      SELECT -- COUNT(*)
      TO_CHAR( FLS.upload_date, 'YYYY' ) Upload_Yr, FDE.entity_name, FDE.table_name, FDS.datatype_ID, PK1_VALUE, PK2_VALUE
      FROM FND_ATTACHED_DOCUMENTS FAD
      , FND_DOCUMENTS FDS
      , FND_LOBS FLS
      , FND_DOCUMENT_ENTITIES FDE
      WHERE 2=2
      AND FLS.file_ID = FDS.media_Id
      AND FAD.document_ID = FDS.document_ID
      -- AND FDS.datatype_ID = 6
      AND FLS.program_name = 'FNDATTCH'
      AND FAD.entity_name = FDE.entity_name
      AND FDE.application_ID = 200
      -- GROUP BY TO_CHAR( FLS.upload_date, 'YYYY' ), FDE.entity_name, FDE.table_name, FDS.datatype_ID
      ORDER BY 1, 2, 3, 4 ;
      1.   
     
    1.     

 

More documentation

To get more information about Payabels Purging, please refer to Chapter 9 - Resource Management of the "Oracle® Payables User's Guide Release 12.2" - Part No. E48760-17; in PDF or Web formats.

Here you will find all about:

  • Criteria for Purging Records
  • Submitting Purges
  • Viewing the Status of a Purge
  • Aborting a Purge / Restarting a Purge
  • Purge Reports: Preliminary Purged Listings, Final Purged Listings, etc.



.

References

NOTE:298698.1 - Avoiding Abnormal Growth of FND_LOBS Table Due To Attachments Data In E-Business Suite Applications
NOTE:1998077.1 - Automated Troubleshooting Process
NOTE:1165208.1 - Concurrent Request "Purge Obsolete Generic File Manager Data" Methods
NOTE:829235.1 - FAQ - Performance Considerations For FND_LOBS
NOTE:242090.1 - SEGMENT SHRINK and Details.
NOTE:2768387.1 - How To Purge FND_LOBS "export" Records With No EXPIRATION_DATE
NOTE:1288149.1 - How To Manage, Reduce, and/or Purged The FND_LOBS Table?
NOTE:752322.1 - Reducing the Oracle E-Business Suite Data Footprint
NOTE:1163933.1 - Table FND_LOBS Shows Columns PROGRAM_NAME And UPLOAD_DATE As Null For Attachments
NOTE:1320736.1 - AP: How To Create and Capture an FND Debug Logfile
NOTE:303709.1 - E-Business Suite Database For Applications Technology Stack Reclaiming Unused Space In A EBS Tablespace By Reducing The Size Of The Datafiles
文章標籤

Somebaby 發表在 痞客邦 留言(0) 人氣()

Applies to:

Oracle Warehouse Management - Version 11.5.10 and later
Information in this document applies to any platform.

Goal

This document will help users to modify mobile  pages without doing customization.

Solution

Attached to this note is a Technical Brief which discusses various scenarios of modifying mobile pages. Mobile Personalization Technical Brief.

文章標籤

Somebaby 發表在 痞客邦 留言(0) 人氣()

Applies to:

Oracle General Ledger - Version 12.2 and later
Information in this document applies to any platform.

Goal

How to disable the consolidation definition?
 

Solution

There is no option to disable the Consolidation definition.

As an workaround we can perform the following steps:

  1. Login to General Ledger responsibility.
  2. Navigate to Consolidation > Define > Consolidation
  3. Consolidation definition form have 'Enable Security'. Users can use this feature to give View Only access to one of the Consolidation definition at the responsibility level. By enabling this feature we can restrict the user from running consolidation program.
  4. In addition to this users can also add a prefix to consolidation definition which you do not want to use.

References

NOTE:2054823.1 - How to Hide the AutoPost Option in the GLXCORUN Transfer Consolidation Data Form?
NOTE:1089532.1 - Assign Access Button is Invisible in the Consolidation Definition Form
NOTE:415901.1 - How to Properly Assign/Remove Definition Access Sets in Oracle General Ledger R12
NOTE:2187030.1 - How to Set Multiple Privileges for Definition Access Sets
NOTE:735944.1 - R12: Definition Access Set Restricts All Responsibilities
NOTE:1951967.1 - How to Make Function Read Only for Chart of Accounts Mappings - Segment Rules (GLXCOMAP)

文章標籤

Somebaby 發表在 痞客邦 留言(0) 人氣()