Posts

Showing posts from March, 2013

Technical Perspective: Transactions

AR_PAYMENT_SCHEDULES

AR_PAYMENT_SCHEDULES
Imp Columns:
payment_schedule_id amount_due_original amount_due_remaining customer_trx_id cash_receipt_id     trx_number status           --Possible Values are CL- Closed , OP-Opened amount_applied    class
AR_PAYMENT_SCHEDULES stores customer balance information at the transaction level. Each transaction's balance is stored as a unique record, based on the primary key, payment_schedule_id.
The class column identifies the transaction type and determines which columns Receivables updates when a transaction is stored.
For billing transactions, the AR_PAYMENT_SCHEDULES table joins the RA_CUSTOMER_TRX table via the customer_trx_id column and stores NULL in the cash_receipt_id column.

For payment transactions, the AR_PAYMENT_SCHEDULES table joins the AR_CASH_RECEIPTS table via the cash_receipt_id column and stores NULL in the customer_trx_id column.

Finding the Patch Level In Oracle apps

Use the Below queries for finding the Oracle Apps patch Levels for a particular Application



SELECT patch_level FROM fnd_product_installations WHERE application_id = 200;

SELECT a.application_name,
  DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
  patch_level ,
  a.application_id
FROM apps.fnd_application_vl a,
  apps.fnd_product_installations b
WHERE a.application_id = b.application_id;


SELECT patch_name,
  patch_type,
  maint_pack_level,
  creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date DESC;

SELECT fa.APPLICATION_SHORT_NAME,
  fpi.PATCH_LEVEL,
  DECODE(fpi.STATUS, 'I','Installed', 'S','Shared', 'N', 'Inactive', fpi.STATUS) Status ,
  fpi.DB_STATUS
FROM fnd_product_installations fpi,
  FND_APPLICATION fa
WHERE fpi.APPLICATION_ID IN
  (SELECT APPLICATION_ID
  FROM FND_APPLICATION
  WHERE APPLICATION_SHORT_NAME IN ('CSK','IBU','BIS',&…

Run Report Server Reports From PL/Sql Script

DECLARE
V_Start_Date_Time    VARCHAR2(100);
V_Gateway            VARCHAR2(1000) :='http://reportstest/reports/rwservlet/';
V_Desname            VARCHAR2(4000);
V_Des_Server   VARCHAR2(4000) :='\\testserver.net\01\Files\AR\XXREPORTS\02\';
--Location of the server to store the Report output we can also pass the email id like xx@maildomain.com
V_Filename VARCHAR2(1000);
V_Userid             VARCHAR2(100):= apps/apps@scott ; --Database login credentials
v_apsuid             VARCHAR2(100) :=;
V_Paramlist          srw_paramlist;
V_Job_Ident          srw.Job_Ident;
V_Sta_Ident          srw.Status_Record;

BEGIN

For I_Index in (SELECT   *
FROM     xxAP_INVOICES
WHERE    1=1
AND      ORG_ID =83
--AND TRUNC(created_date) = TRUNC(SYSDATE) - 1 ; --AND      Request_id =13158029; -- TRUNC(created_date) = TRUNC(SYSDATE) - 0;
AND      Request_id =10701633)
LOOP

V_Filename  :=TO_CHAR(I_Index.invoice_date,'DD.MM.RRRR')||'_'||I_Index.customer_number||'.pdf';
V_Desname  …

External Table Example

CREATE TABLE "XX_STAGE_SUPPLIERS"
(
"SUPPLIER_NAME" VARCHAR2(200),
"SUPPLIER_NO" VARCHAR2(50)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY XXBMASSINV
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE ' XXBMASSINV':'VENDOR7.bad'
LOGFILE 'VENDOR7.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"SUPPLIER_NAME" CHAR(200)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SUPPLIER_NO" CHAR(255)

)
)
location
(
'table_export777.csv'
)
);



/*--Sample Data


"HOLCH POVLSEN SWITZERLAND AG","410025"
"ABS TRIKO TEKSTIL","253410"
"AKTIESELSKABET AF 21.11.2001","008067"
"AKTIESELSKABET AF DEN 17.11.2011","99423204"
"RIMAKS","253606"
"GULEKS TEKSTIL TIC. VE SA…

Sub Templates In Oracle BI

There May be scenarios that where and when we have some part of XML data needs to be represent in fixed format and remaining XML data needs to be represent in multiple formats based on some conditions (let us say for each country specific remaining data XML data needs to be processed) in that case  once after registering the concurrent program (either rdf or Data template type) we can create one Default data template for the concurrent program  and other multiple sub templates for each country specific .

 Create one Default or main Data template for concurrent program along with the multiple sub templates for the same data definition in oracle apps.

We can create sub templates in the oracle apps by selecting sub template option as "Yes" while creating template using Xml Publisher Administrator responsibility.

While creating the main or Default Data template RTF file first we have to import all the sub templates that are going to be called in this as below.

<?import:xdo://XXPI…