Commonly used Predefined CSS Classes in Oracle Application Framework

AR_MISC_CASH_DISTRIBUTIONS table overview

AR_MISC_CASH_DISTRIBUTIONS
misc_cash_distribution_id	cash_receipt_id	code_combination_id

AR_MISC_CASH_DISTRIBUTIONS stores the accounting distribution for miscellaneous cash receipts.

Each distribution is stored as a unique record, based on the primary key, misc_cash_distribution_id.

The distributions are linked to the receipt by the column cash_receipt_id. The code_combination_id column stores the general ledger account assigned to this receipt.

AR_CASH_RECEIPT_HISTORY table overview

AR_CASH_RECEIPT_HISTORY
cash_receipt_history_id	amount	status

AR_CASH_RECEIPT_HISTORY stores the current status and history of a receipt. Each status change is stored as a unique transaction, based on the primary key, cash_receipt_history_id. The status column describes which step of the receipt's life cycle the receipt has reached. Valid status values are:

• APPROVED - This is only valid for automatic receipts and signifies the receipt has been approved for automatic creation. These record types are never postable.

• CONFIRMED - This is only valid for automatic receipts and signifies the receipt has been confirmed by the customer.

• REMITTED - This is valid for both manual and automatic receipts and signifies the receipt has been remitted.

• CLEARED - This is valid for both manual and automatic receipts and signifies the receipt has been cleared.

• REVERSED - This is valid for both manual and automatic receipts and signifies the receipt has been reversed.

As the receipt moves through its life cycle, Receivables inserts a new record into AR_CASH_RECEIPTS_HISTORY with the current_record_flag column set to 'Y'. Receivables also updates the previous record related to this receipt, by setting the current_record_flag to NULL and by setting the reversal_gl_date. The amount column stores the amount of the receipt. The cash_receipts_id column links

AR_CASH_RECEIPTS_HISTORY to AR_CASH_RECEIPTS.

AR_CASH_RECEIPTS table overview

AR_CASH_RECEIPTS
cash_receipt_id	amount	status	receipt_number	type

AR_CASH_RECEIPTS stores a unique record for each receipt, based on the primary key, cash_receipt_id. The status column describes the state of the receipt in relation to customer invoices and balances. Valid status values are:

• UNID - The receipt customer is unidentified and no customer balance has been updated.

• UNAPP - The receipt customer has been identified, but the receipt has not been entirely applied to a specific invoice or been placed on account.

• APP - The entire amount of the receipt has been placed on account or applied to specific customer invoices.

• REV - The receipt has been reversed.

• NSF - The receipt has been reversed due to insufficient funds.

• STOP - The receipt has been reversed by a stop payment.

The type column identifies the receipt as either CASH or MISC to indicate whether the receipt is a customer payment or a miscellaneous receipt (not related to a receivable activity). The amount column stores the net amount of the receipt, while the receipt_number column stores the receipt_number.

AR_CREDIT_MEMO_AMOUNTS table Overview

AR_CREDIT_MEMO_AMOUNTS
credit_memo_amount_id	customer_trx_line_id	gl_date	amount

AR_CREDIT_MEMO_AMOUNTS stores the GL dates and amounts for credit memos to use when
they are applied to invoices with rules.

Each credit memo application date is stored as a unique record, based on the primary key, credit_memo_amount_id.

The customer_trx_line_id references the transaction line to which this credit memo applies.

The gl_date column stores the date the credit memo should be applied to the invoice and the amount column stores the amount to apply.

AR_RECEIVABLE_APPLICATIONS table overview

AR_RECEIVABLE_APPLICATIONS
receivable_ application _id	amount_ applied	status	payment_ schedule_ id	code_ combination_ id	cash_ receipt _id	applied_ payment_ schedule_ id	applied_ customer_ trx_id

AR_RECEIVABLE_APPLICATIONS stores account distributions for receipt and credit memo applications and maps the application transaction to the applied transaction. Each accounting distribution is stored as a unique record, based on the primary key, receivable_application_id.

The payment_schedule_id column links the receipt or credit memo to its payment schedule in the AR_PAYMENT_SCHEDULES table. The cash_receipt_id column stores the receipt id of payment transactions, while the cust_trx_id column, which is not shown, stores the transaction id for credit memo transactions.

The applied_payment_schedule_id and applied_customer_trx_id columns reference the transaction to which this record applies.

The status column describes the state of the application transaction. For credit memos, the status will always be APP to identify the credit memo as applied. For receipt transactions, valid status values are APP, UNAPP, UNID, REV, NSF, and STOP.

The code_combination_id column stores the general ledger account for the application transaction, based on the status. The amount_applied column stores the amount of the receipt or credit memo as a positive value.

For cash basis accounting, Receivables uses the table AR_CASH_BASIS_DISTRIBUTIONS to store account distribution information. This table shows the distribution to revenue accounts of a given receipt based on the application of the receipt.

AR_ADJUSTMENTS table overview

AR_ADJUSTMENTS
adjustment_id	amount	customer_trx_id	type	payment_schedule_id	code_combination_id

AR_ADJUSTMENTS stores information about invoice adjustments. Each adjustment is stored as a unique record, based on the primary key, adjustment_id. The amount column stores the amount of the adjustment. Receivables uses the customer_trx_id and payment_schedule_id to link the adjustment to the adjusted transaction and to update the amount_due_remaining and amount_adjusted columns of the adjusted transaction's payment schedule in the AR_PAYMENT_SCHEDULES table. The type column stores a description of the transaction to which the adjustment applies. Valid types include:

• Charges Adjustments

• Freight Adjustments

• Invoice Adjustments

• Line Adjustments

• Tax Adjustments

The code_combination_id column stores the accounting distribution associated with the adjustment transaction.

AR_PAYMENT_SCHEDULES table overview

AR_PAYMENT_SCHEDULES
payment_ schedule _id	amount_ due_ original	amount_ due_ remaining	customer _trx_id	cash_ receipt_ id	trx_ number	status	amount_ applied	class

This table stores all transactions except adjustments and miscellaneous cash receipts. AR_PAYMENT_SCHEDULES stores customer balance information at the transaction level. Oracle Receivables updates this table when activity occurs against an invoice, debit memo, chargeback, credit memo, on account credit, or receipt. Oracle Receivables groups different transactions by the column CLASS.

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.

TRANSACTION	CLASS	FOREIGN KEY	TABLE
Invoices	INV	customer_trx_id	RA_CUSTOMER_TRX
Debit Memos	DM	customer_trx_id	RA_CUSTOMER_TRX
Credit Memos	CM	customer_trx_id	RA_CUSTOMER_TRX
Deposits	DEP	customer_trx_id	RA_CUSTOMER_TRX
Guarantees	GUAR	customer_trx_id	RA_CUSTOMER_TRX
Chargebacks	CB	customer_trx_id	RA_CUSTOMER_TRX
Receipts	PMT	cash_receipts_id	AR_CASH_RECEIPTS

The status column identifies whether the transaction is open or closed, while the trx_number column stores the transaction number. The amount_applied column stores the sum of all transactions applied to the balance of the selected transaction. The amount_due_original column equals either the sum of the extended_amount column in the RA_CUSTOMER_TRX_LINES table for the given customer_trx_id or the sum of the amount column in the AR_CASH_RECEIPTS table for the given cash_receipts_id. The amount_due_remaining column represents the balance for the selected transaction.

For the amount_due_original and amount_due_remaining columns debit items, such as invoices, are stored as positive numbers and credit items, such as credit memos and payments, are stored as negative numbers. The current customer balance is reflected by the sum of the amount_due_remaining column for all confirmed payment schedules for a given customer.
When a receipt is applied, Oracle Receivables updates AMOUNT_APPLIED, STATUS and AMOUNT_DUE_REMAINING. STATUS changes from ’OP’ to ’CL’

RA_CUST_TRX_LINE_SALESREPS_ALL table overview

RA_CUST_TRX_LINE_SALESREPS_ALL
cust_trx_line_ salesrep_id	sales_rep_id	customer_trx _line_id	revenue_ amount_split	non_revenue_ amount_split	prev_cust_trx_line _salesrep_id

RA_CUST_TRX_LINE_SALESREPS stores sales credit assignments for invoice lines. Each assignment is stored as a unique record, based on the primary key, cust_trx_line_salesrep_id. If you base your accounting distributions on sales credits, the sales credit assignments in this table map to the RA_CUST_TRX_LINE_GL_DIST table. The sales_rep_id column identifies the salesperson receiving the credit for this transaction. The customer_trx_line_id column is a foreign key to the RA_CUSTOMER_TRX_LINES table.

The revenue_amount_split column stores the amount of the invoice line assigned to this salesperson. The non_revenue_amount_split column stores the amount of the non-header freight and tax lines assigned to this salesperson. If the sales credit were derived based on a percentage of the transaction line rather than a specific amount, the columns revenue_percent_split and non_revenue_percent_split would store the percentages of the transaction lines assigned to this salesperson. The prev_cust_trx_line_salesrep_id column references another sales credit assignment to which the current record is being applied.

RA_CUST_TRX_LINE_GL_DIST_ALL table Overview

RA_CUST_TRX_LINE_GL_DIST_ALL
cust_trx_line_gl_dist_id	code_combination_id	customer_trx_line_id	account_class	amount
CUST_TRX_LINE_SALESREP_ID

RA_CUST_TRX_LINE_GL_DIST_ALL stores the accounting distribution for invoice, debit memo, commitment, and credit memo transactions. Each distribution is stored as a unique record, based on the primary key, cust_trx_line_gl_dist_id. The customer_trx_line_id column is a foreign key to the RA_CUSTOMER_TRX_LINES_ALL table.

The account_class column describes the account type, while the code_combination_id column identifies the general ledger account. Valid account classes are CHARGES, FREIGHT, REC, REV, SUSPENSE, TAX, UNBILL and UNEARN.

The account_class, REC, represents the receivable account distribution. The amount column for REC records is equal to the sum of all invoice lines. Therefore, there is no link to RA_CUSTOMER_TRX_LINES_ALL and the column customer_trx_line_id is null for these records. The REC record is linked to the table, RA_CUSTOMER_TRX_ALL, via the customer_trx_id column. For all other account classes, credits are represented by positive numbers and debits are represented by negative numbers.

RA_CUSTOMER_TRX_LINES_ALL Table OverView

RA_CUSTOMER_TRX_LINES_ALL
customer_trx_line_id	customer_trx_id	link_to_cust_trx_line_id	line_type	extended_amount

The RA_CUSTOMER_TRX_LINES_ALL table stores invoice, debit memo, commitment and credit memo line level information. Each transaction line is stored as a unique record, based on the primary key, customer_trx_line_id column. The customer_trx_id column is a foreign key to the RA_CUSTOMER_TRX_ALL table.

The line_type column identifies the type of data contained in the record. Valid line types are CHARGES, FREIGHT, LINE and TAX. Any record with a line type of TAX or FREIGHT refers to the original invoice line via the link_to_cust_trx_line_id column, except for header freight transactions. The total amount for each transaction line is stored in the column extended_amount.

RA_CUSTOMER_TRX_ALL table overview

RA_CUSTOMER_TRX_ALL
customer_trx_id	trx_number	bill_to_customer_id	trx_date

The RA_CUSTOMER_TRX_ALL table stores invoice, debit memo, commitment and credit memo header information. Each row includes general invoice information such as customer, transaction type, and printing instructions.

Invoices, debit memos, credit memos, and commitments are all distinguished by their transaction types stored in RA_CUST_TRX_TYPES_ALL (cust_trx_type_id).

COMPLETE_FLAG stores ’Y’ for Yes and ’N’ for No to indicate if your invoice is complete. When you complete an invoice, Oracle Receivables creates your payment schedules and updates any commitments against this invoice. Before an invoice can be completed, it must have at least one invoice line, revenue records must exist for each line and add up to the line amount, and a sales tax record must exist for each line.

Each of the transactions is stored as a unique record, based on the primary key, customer_trx_id. The transaction number, transaction date and billing customer are stored in the trx_number, trx_date and bill_to_customer_id columns, respectively.

Additional information stored in this table includes ship-to customer, document sequence number, currency code and a transaction complete flag.

SOLD_TO_CUSTOMER_ID, SOLD_TO_SITE_USE_ID, BILL_TO_CUSTOMER_ID, BILL_TO_SITE_USE_ID, SHIP_TO_SITE_USE_ID, PRINTING_OPTION, PRINTING_PENDING, TERM_ID, REMIT_TO_ADDRESS_ID, PRIMARY_SALES_REP_ID, and INVOICE_CURRENCY_CODE are required even though they are null allowed.

(AR) Account Receivable Important tables in Oracle APPS

• TRANSACTIONS(AR INVOICES)

Select * from   RA_CUSTOMER_TRX_ALL

Select * from   RA_CUSTOMER_TRX_LINES_ALL

Select * from   RA_CUST_TRX_LINE_GL_DIST_ALL

Select * from   RA_CUST_TRX_LINE_SALESREPS_ALL

• AR CASH RECEIPTS

Select * from   AR_CASH_RECEIPTS_ALL

• AR INVOICE TYPES

Select * from   RA_CUST_TRX_TYPES_ALL

• AR BATCH SOURCE

Select * from   RA_BATCH_SOURCES_ALL

• AR SALES REPS

Select * from   RA_SALESREPS_ALL

• AR PAYMENT TERMS

Select * from   RA_TERMS_TL

Select * from   RA_TERMS_LINES

• AR RECEIPT PAYMENT METHOD

Select * from   AR_RECEIPT_METHODS

Query to get Concurrent Manager Status in Oracle APPS

  SELECT q.concurrent_queue_id,
         q.concurrent_queue_name,
         q.user_concurrent_queue_name,
         q.target_node,
         q.max_processes,
         q.running_processes,
         running.run running,
         pending.pend,
         DECODE (q.control_code,
                 'D', 'Deactivating',
                 'E', 'Deactivated',
                 'N', 'Node unavai',
                 'A', 'Activating',
                 'X', 'Terminated',
                 'T', 'Terminating',
                 'V', 'Verifying',
                 'O', 'Suspending',
                 'P', 'Suspended',
                 'Q', 'Resuming',
                 'R', 'Restarting')
            status
    FROM (  SELECT concurrent_queue_name, COUNT (phase_code) run
              FROM fnd_concurrent_worker_requests
             WHERE     phase_code = 'R'
                   AND hold_flag != 'Y'
                   AND requested_start_date <= SYSDATE
          GROUP BY concurrent_queue_name) running,
         (  SELECT concurrent_queue_name, COUNT (phase_code) pend
              FROM fnd_concurrent_worker_requests
             WHERE     phase_code = 'P'
                   AND hold_flag != 'Y'
                   AND requested_start_date <= SYSDATE
          GROUP BY concurrent_queue_name) pending,
         apps.fnd_concurrent_queues_vl q
   WHERE     q.concurrent_queue_name = running.concurrent_queue_name(+)
         AND q.concurrent_queue_name = pending.concurrent_queue_name(+)
         AND q.enabled_flag = 'Y'
ORDER BY DECODE (q.application_id,
                 0, DECODE (q.concurrent_queue_id,  1, 1,  4, 2)),
         SIGN (q.max_processes) DESC,
         q.concurrent_queue_name,
         q.application_id;

How to check Built-In functions available in Python

>>> dir(__builtins__)

String Escaping in Python

String Escaping in Python:-

Escape Sequence	Output
\\	Backslash
\'	Single Quote
\"	Double Quote
\b	ASCII Backspace
\n	Newline
\t	Tab
\u12af	Unicode 16 bit
\U12af89bc	Unicode 32 bit
\o84	Octal character
\xFF	Hex character

Python Environment Variables

Below are the Python Environment Variables:-

• PYTHONPATH:- Has a role similar to PATH. This variable tells the Python interpreter where to locate the module files you import into a program. PYTHONPATH should include the Python source library directory and the directories containing your Python source code. PYTHONPATH is sometimes preset by the Python installer. 

• PYTHONSTARTUP:- Contains the path of an initialization file containing Python source code that is executed every time you start the interpreter (similar to the Unix .profile or .login file). This file, often named .pythonrc.py in Unix, usually contains commands that load utilities or modify PYTHONPATH.

• PYTHONCASEOK:- Used in Windows to instruct Python to find the first case-insensitive match in an import statement. Set this variable to any value to activate it.

• PYTHONHOME:- An alternative module search path. It's usually embedded in the PYTHONSTARTUP or PYTHONPATH directories to make switching module libraries easy.

API to Book the Sales Order in Oracle Apps

DECLARE
   v_api_version_number           NUMBER := 1;
   v_return_status                VARCHAR2 (2000);
   v_msg_count                    NUMBER;
   v_msg_data                     VARCHAR2 (2000);

   -- IN Variables --
   v_header_rec                   oe_order_pub.header_rec_type;
   v_line_tbl                     oe_order_pub.line_tbl_type;
   v_action_request_tbl           oe_order_pub.request_tbl_type;
   v_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;

   -- OUT Variables --
   v_header_rec_out               oe_order_pub.header_rec_type;
   v_header_val_rec_out           oe_order_pub.header_val_rec_type;
   v_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
   v_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
   v_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
   v_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
   v_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
   v_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
   v_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
   v_line_tbl_out                 oe_order_pub.line_tbl_type;
   v_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
   v_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
   v_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
   v_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
   v_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
   v_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
   v_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
   v_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
   v_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
   v_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
   v_action_request_tbl_out       oe_order_pub.request_tbl_type;
  
   l_user_id        NUMBER;
   l_resp_id        NUMBER;
   l_resp_appl_id   NUMBER;
   l_line_tbl_index NUMBER := 0;
   
  CURSOR book_order
  IS
   SELECT header_id, order_number
     FROM oe_order_headers_all
    WHERE order_number IN ('1001001','1001002');


BEGIN
  
   BEGIN
    SELECT user_id
      INTO l_user_id
      FROM fnd_user
     WHERE user_name = 'XXUSER';    
   END;
  
   BEGIN
    SELECT responsibility_id, application_id
      INTO l_resp_id, l_resp_appl_id
      FROM fnd_responsibility_tl
     WHERE responsibility_name = 'Order Management Super User'  ; 
   END;
  
   DBMS_OUTPUT.PUT_LINE ('Starting of script');

   -- Setting the Enviroment --
  
   mo_global.init ('ONT');
  
   fnd_global.apps_initialize (l_user_id
                              ,l_resp_id
                              ,l_resp_appl_id
                              ) ; -- pass in user_id, responsibility_id, and application_id

   mo_global.set_policy_context ('S', 121);
 
    FOR i IN book_order
     LOOP
           l_line_tbl_index := l_line_tbl_index +1;
           v_action_request_tbl(l_line_tbl_index) := oe_order_pub.g_miss_request_rec;
           v_action_request_tbl(l_line_tbl_index).request_type := OE_GLOBALS.G_BOOK_ORDER;
           v_action_request_tbl(l_line_tbl_index).entity_code  := OE_GLOBALS.G_ENTITY_HEADER;
           v_action_request_tbl(l_line_tbl_index).entity_id    := i.header_id;

           DBMS_OUTPUT.PUT_LINE ('Starting of API');

           -- Calling the API to to Book an Existing Order --

           OE_ORDER_PUB.PROCESS_ORDER (
              p_api_version_number       => v_api_version_number,
              p_header_rec               => v_header_rec,
              p_line_tbl                 => v_line_tbl,
              p_action_request_tbl       => v_action_request_tbl,
              p_line_adj_tbl             => v_line_adj_tbl-- OUT variables
              ,
              x_header_rec               => v_header_rec_out,
              x_header_val_rec           => v_header_val_rec_out,
              x_header_adj_tbl           => v_header_adj_tbl_out,
              x_header_adj_val_tbl       => v_header_adj_val_tbl_out,
              x_header_price_att_tbl     => v_header_price_att_tbl_out,
              x_header_adj_att_tbl       => v_header_adj_att_tbl_out,
              x_header_adj_assoc_tbl     => v_header_adj_assoc_tbl_out,
              x_header_scredit_tbl       => v_header_scredit_tbl_out,
              x_header_scredit_val_tbl   => v_header_scredit_val_tbl_out,
              x_line_tbl                 => v_line_tbl_out,
              x_line_val_tbl             => v_line_val_tbl_out,
              x_line_adj_tbl             => v_line_adj_tbl_out,
              x_line_adj_val_tbl         => v_line_adj_val_tbl_out,
              x_line_price_att_tbl       => v_line_price_att_tbl_out,
              x_line_adj_att_tbl         => v_line_adj_att_tbl_out,
              x_line_adj_assoc_tbl       => v_line_adj_assoc_tbl_out,
              x_line_scredit_tbl         => v_line_scredit_tbl_out,
              x_line_scredit_val_tbl     => v_line_scredit_val_tbl_out,
              x_lot_serial_tbl           => v_lot_serial_tbl_out,
              x_lot_serial_val_tbl       => v_lot_serial_val_tbl_out,
              x_action_request_tbl       => v_action_request_tbl_out,
              x_return_status            => v_return_status,
              x_msg_count                => v_msg_count,
              x_msg_data                 => v_msg_data);

           DBMS_OUTPUT.PUT_LINE ('Completion of API');


           IF v_return_status = fnd_api.g_ret_sts_success
           THEN
              COMMIT;
              DBMS_OUTPUT.put_line ('Order '||i.order_number||' is Success..');
           ELSE
              DBMS_OUTPUT.put_line ('Order '||i.order_number||' is Failed..' || v_msg_data);
              ROLLBACK;

              FOR i IN 1 .. v_msg_count
              LOOP
                 v_msg_data := oe_msg_pub.get (p_msg_index => i
                                              ,p_encoded => 'F'
                                              );
                 DBMS_OUTPUT.put_line (i || ') ' || v_msg_data);
              END LOOP;
           END IF;
     END LOOP;
    
END;
/

Commit