sales amount for customer (RA Headers/Lines) Revenue tracked from Invoices

SELECT hp.party_name customer_name, rt.NAME payment_terms,      

       sum(lgd.amount)

  FROM ra_customer_trx_all rct,

       ra_cust_trx_types_all rctt,

       hz_cust_accounts_all hca,

       ra_customer_trx_lines_all rctl,

       ra_customer_trx_lines_all tax_rctl,

       ra_customer_trx_lines_all fr_rctl,

       ra_terms rt,

       hz_parties hp,

       ra_cust_trx_line_gl_dist_all lgd,

       ra_customer_trx_lines ctl_line,

       ar_lookups al_class,

       ar_lookups al_type,

       ra_rules rr

 WHERE rctt.cust_trx_type_id = rct.cust_trx_type_id

   AND rct.sold_to_customer_id = hca.cust_account_id

   AND rct.customer_trx_id = rctl.customer_trx_id

   AND tax_rctl.customer_trx_id(+) = rct.customer_trx_id

   AND tax_rctl.line_type(+) = 'TAX'

   AND fr_rctl.customer_trx_id(+) = rct.customer_trx_id

   AND fr_rctl.line_type(+) = 'FREIGHT'

   AND hca.account_number = 12345

   AND rct.trx_date between to_date('01-JAN-2015','DD-MON-YYYY') and to_date('30-JUN-2015','DD-MON-YYYY')

   AND rct.term_id = rt.term_id

   AND hp.party_id = hca.party_id

   AND lgd.customer_trx_line_id = rctl.customer_trx_line_id (+)

   --AND rct.customer_trx_id = 7451947

   AND rctl.link_to_cust_trx_line_id = ctl_line.customer_trx_line_id(+)

   AND al_class.lookup_type = 'AUTOGL_TYPE'

   AND al_class.lookup_code = lgd.account_class

   AND al_type.lookup_type (+)= 'STD_LINE_TYPE'

   AND al_type.lookup_code (+) = rctl.line_type

   AND rctl.accounting_rule_id = rr.rule_id (+)

   AND lgd.account_class = 'REV'  

   group by hp.party_name, rt.NAME

XML publisher password protected documents in oracle apps

1)Open any .rtf
2)Go to File -> Info -> Properties -> Advanced Properties.

3)Now go to custom menu and define the below two custom properties.

 i. Name: xdo-pdf-open-password Type : Text Value : It can be either hardcoded value or the XML          data value.

Note : If it is XML data value then you should enclose it in {} but not in XML tags(i.e <>)

You can see from the below screen , here I am using order_number data from XML as {order_number}.

ii. Name: xdo-pdf-security Type :Text Value :true

11i and R12-Changes

• 11i and R12
  
  In 11i the field 'bank_account_id' acts as a relation key between the AP_CHECKS_ALL and AP_BANK_ACCOUNTS_ALL tables. 
  
  In 12 i the same field is no longer is used in 'AP_CHECKS_ALL' table.
  
  In 12 i where 'CE_BANK_ACCT_USE_ID' is acts as a relation between 'CE_BANK_ACCT_USES_ALL' and 'AP_CHECKS_ALL'
  
  Highlights of release 12i
  
  A single responsibility to access and transact on multiple organizations.
  A single ledger to manage multiple currencies.
  Ledger sets to manage accounting processes across ledger.
  Centralized rules engines for tax, accounting and Intercomapny.
  Centralized trading partners i.e Suppliers, Banks, First Party legal entities.
  Simplified reporting via XML Publisher and DBI.
  Netting across trading partners.

Creating a Periodic Alert in R12

To create a periodic alert, you perform the following tasks in the order listed:

• Define your periodic alert and specify its frequency.
• Specify the details for your alert.
• Define actions for your alert.
• Create action sets containing the actions you want your alert to perform.

Before you define a periodic alert, make sure you do the following:





















Configure the Workflow Notification Mailer to send and receive e-mail messages according to your alert requirements. 
• Specify Oracle Alert options to configure how Oracle Alert checks alerts and handles alert messages.
Enter a SQL Select statement that retrieves all the data your alert needs to perform the actions you plan to define.
Your periodic alert Select statement must include an INTO clause that contains one output for each column selected by your Select statement. Identify any inputs with a colon before the name, for example, :INPUT_NAME. Identify any outputs with an ampersand (&) before the name, for example, &OUTPUT_NAME. Do not use set operators in your Select statement.
Tip: If you want to use an input value in an action for this alert, select the input into an output. Then you can use the output when you define actions for this alert.
When selecting number columns, Oracle Alert uses the number formats defined in your database. Optionally, you can format your number outputs as real numbers by specifying a SQL*Plus format mask in your Select statement. For each number output, simply add a pound sign (#) and format mask to your output name. For example, if you select purchase price into the output &PRICE, add "#9999.99" after &PRICE for Oracle Alert to display the value to two
decimal places. Your number output looks like: &PRICE#9999.99. Here is an example of a periodic alert Select statement that looks for users who have not changed their passwords within the number of days specified by the value in :THRESHOLD_DAYS.:
SELECT user_name,
password_date,
:THRESHOLD_DAYS
INTO &USER,
&LASTDATE,
&NUMDAYS
FROM fnd_user
WHERE sysdate = NVL(password_date,
sysdate) + :THRESHOLD_DAYS
ORDER BY user_name
Note: Although Oracle Alert does not support PL/SQL statements as the alert SQL statement definition, you can create a PL/SQL packaged function that contains PL/SQL logic and enter a SQL Select statement that calls that packaged function. For example, you can enter a SQL Select statement that looks like:
SELECT package1.function1(:INPUT1, column1)
INTO &OUTPUT1
FROM table1
In this example, package1 is the name of the PL/SQL package and function1 is the name of user-defined PL/SQL function stored in the package.

Alerts in oracle

Oracle Alert is your complete exception control solution. Alerts signal important orunexpected activity in your database. They ensure that you are regularly and quickly informed about critical database events instead of sorting through length reports.
The basic function of alerts includes but not limited to

1. Keep you informed of critical activity in your database
2. Deliver key information from your applications, in the format you choose
3. Provide you with regular reports on your database information
4. Automate system maintenance, and routine online tasks

You can define one of two types of alerts: an event alert or a periodic alert.
An event alert immediately notifies you of activity in your database as it occurs. When you create an event alert, you specify the following:

• A database event that you want to monitor, that is, an insert or an update to a specific database table.
• A SQL Select statement that retrieves specific database information as a result of the database event.
• Actions that you want Oracle Alert to perform as a result of the database event. An action can entail sending someone an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. You include all the actions you want Oracle Alert to perform, in an action set.

A periodic alert, on the other hand, checks the database for information according to a schedule you define. When you create a periodic alert, you specify the following:

Write message in Log or Out file

When ever, I have to create new package, I normally code a procedure to write any information in the log or Out files of the program.

The following is the Procedure which I use it in any package. This is standard in some companies coding. This save a lot of time and this makes easy to other people who goes through your program.

PROCEDURE write(p_type IN VARCHAR2, p_message IN VARCHAR2)
IS
/************************************************************************
Purpose : Procedure writes to the log file or output file
based on type.O=Output File, L=Log File
*************************************************************************/

BEGIN
IF p_type = 'L'
THEN


fnd_file.put_line (fnd_file.log, p_message);

ELSIF p_type = 'O'
THEN

fnd_file.put_line (fnd_file.output, p_message);


END IF;
END write;

The above write procedure can be used in other Procedure/Function in the package to write any information in the Log or Out files. 

PROCEDURE main(errbuf OUT VARCHAR2
, retcode OUT NUMBER
, p_par1 IN NUMBER
)
IS
v_errbuf VARCHAR2(1000) := NULL;
v_retcode NUMBER := 0;
v_file_name VARCHAR2(100);
BEGIN

v_retcode := 0;

v_file_name := fnd_profile.value('XYZ');


IF v_file_name IS NULL
THEN
write('O','Profile XYZ is not defined or the value is not set');
retcode := 2;
RETURN;
END IF;
END;

Note:- In the above Procedure, I am using the write Procedure and returning 2 for the retcode (0 - Complete, 1- Warning and 2 will be for Error).

Note:- This is one time process and you will realise, how much helpful it will be when ever you have to right something in log or out file.

More then information, this post is more of suggestion. Hope this post will help you make your code easy. 

API to cancel AP Invoice

AP_CANCEL_PKG.IS_INVOICE_CANCELLABLE:
Is_Invoice_Cancellable is a Function in the AP_CANCEL_PKG package that checks that an Invoice is cancellable or not when an Invoice Cancellation process starts.
It follows the following steps and returns a Boolean value depending on the result.

• If invoice contains distribution that does not have open GL period return FALSE.
• If invoice has an effective payment, return FALSE.
• If invoice is selected for payment, return FALSE.
• If invoice is already cancelled, return FALSE.
• If invoice is credited invoice, return FALSE.
• If invoices have been applied against this invoice, return FALSE.
• If invoice is matched to Finally Closed PO’s, return FALSE.
• If project related invoices have pending adjustments, return FALSE.
• If cancelling will cause qty_billed or amount_billed to less than 0, return FALSE.
• If none of above, invoice is cancellable return True.

Here is a small procedure to check if an Invoice is cancellable or not.
 create or replace procedure XX_INV_CANCELLABLE (p_inv_id IN NUMBER)
is
v_boolean               BOOLEAN;
v_error_code            VARCHAR2(100);
v_debug_info            VARCHAR2(1000);
begin
v_boolean :=AP_CANCEL_PKG.IS_INVOICE_CANCELLABLE(
                P_invoice_id       => p_inv_id,
                P_error_code       => v_error_code,
                P_debug_info       => v_debug_info,
                P_calling_sequence => NULL);
IF v_boolean=TRUE
THEN
DBMS_OUTPUT.put_line ('Invoice '||p_inv_id|| ' is cancellable' );
ELSE
DBMS_OUTPUT.put_line ('Invoice '||p_inv_id|| ' is not cancellable :'|| v_error_code );
END IF;
End XX_INV_CANCELLABLE;

Execute XX_INV_CANCELLABLE(12960);


AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE:
AP_CANCEL_SINGLE_INVOICE is a Function in the AP_CANCEL_PKG package that cancels one invoice by executing the following sequence of steps, returning TRUE if successful and FALSE otherwise.
1] Check if the invoice is cancellable. if yes, proceed otherwise return false.
2] If invoice has tax withheld, undo withholding.
3] Clear out all payment schedules.
4] Cancel all the non-discard lines.

a. reverse matching

b. fetch the maximum distribution line number

c. Set encumbered flags to ‘N’

d. Accounting event generation

e. reverse the distributions

f. update Line level Cancelled information

5] Zero out the Invoice.
6] Run AutoApproval for this invoice.
7] Check posting holds remain on this cancelled invoice.

a. if NOT exist – complete the cancellation by updating header level information set return value to TRUE.

b. if exist – no update, set the return values to FALSE, NO DATA rollback.

8] Commit the Data.
9] Populate the out parameters.
Here is a small procedure to cancel a single invoice.

create or replace procedure XX_INV_CANCEL(
                            P_xx_invoice_id IN NUMBER,
                            P_xx_last_updated_by IN  NUMBER,
                            P_xx_last_update_login IN  NUMBER,
                            P_xx_accounting_date IN  DATE)
is
v_boolean               BOOLEAN;
v_message_name          VARCHAR2(1000);
v_invoice_amount        NUMBER;
v_base_amount           NUMBER;
v_temp_cancelled_amount NUMBER;
v_cancelled_by          VARCHAR2(1000);
v_cancelled_amount      NUMBER;
v_cancelled_date        DATE;
v_last_update_date      DATE;
v_orig_prepay_amt       NUMBER;
v_pay_cur_inv_amt       NUMBER;
v_token                 VARCHAR2(100);
begin
v_boolean := AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE
            (p_invoice_id                 => P_xx_invoice_id,
             p_last_updated_by            => P_xx_last_updated_by,
             p_last_update_login          => P_xx_last_update_login,
             p_accounting_date            => P_xx_accounting_date,
             p_message_name               => v_message_name,
             p_invoice_amount             => v_invoice_amount,
             p_base_amount                => v_base_amount,
             p_temp_cancelled_amount      => v_temp_cancelled_amount,
             p_cancelled_by               => v_cancelled_by,
             p_cancelled_amount           => v_cancelled_amount,
             p_cancelled_date             => v_cancelled_date,
             p_last_update_date           => v_last_update_date,
             p_original_prepayment_amount => v_orig_prepay_amt,
             p_pay_curr_invoice_amount    => v_pay_cur_inv_amt,
             P_Token                      => v_token,
             p_calling_sequence           => NULL
             );

IF v_boolean
THEN
DBMS_OUTPUT.put_line ('Successfully Cancelled the Invoice' );
COMMIT;
ELSE
DBMS_OUTPUT.put_line ('Failed to Cancel the Invoice' );
ROLLBACK;
END IF;

end XX_INV_CANCEL;

Execute XX_INV_CANCEL(120573,2325,-1,SYSDATE);
 

On-hand Quatity of one Particular Item in whole Organization

The following Query will On-hand Quatity of particular item in the whole Organization

select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 'Your Inventory Organization ID';

Example:-

Note:- 123456 is the item ID of the particular item. This can be found in the base table of the item (MTL_SYSTEM_ITEMS_B). SEGMENT1 column of this table would have Item Name and Inventory_item_id is the primary column of the table. The follow query is using this value.

select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 123456;

If you want to know the on-hand Quantity of particular item at all the Inventory Organization then use the following the Query.

select sum(primary_transaction_quantity), organization_id from mtl_onhand_quantities_detail where inventory_item_id = 'Your Inventory Organization ID'
group by organization_id;

To know the Application version before upgrade and current version

If there is some oracle Aplication Instance say (11.5.10) got upgrade to R12. After upgarding the Instance/Application, If I want to know the older version. Then use the following select statement.

SELECT SUBSTR(snapshot_name,12,8)
FROM ad_snapshots
WHERE comments = 'Preseeded'
AND REPLACE(SUBSTR(snapshot_name,12,8),'.') =
(
SELECT max(to_number(replace(SUBSTR(snapshot_name,12,8),'.')))
FROM ad_snapshots
WHERE comments = 'Preseeded'
);

Use the following to know the current Oracle Apps Version.
---------------------------------------------------------------------

SELECT release_name FROM fnd_product_groups;