Friday, 23 August 2013

PO Matching Report

1 comments
SELECT
      PHA.TYPE_LOOKUP_CODE,
      PHA.LAST_UPDATE_DATE,
      PHA.SEGMENT1,
      PHA.SUMMARY_FLAG,
      PHA.ENABLED_FLAG,
      PHA.APPROVED_FLAG,
      PLA.UNIT_MEAS_LOOKUP_CODE,    
      PLA.QUANTITY,
      PLA.UNIT_PRICE,
      PLA.LIST_PRICE_PER_UNIT,
      PLA.PRICE_TYPE_LOOKUP_CODE,
      PLL.RECEIPT_REQUIRED_FLAG,
      PLL.INSPECTION_REQUIRED_FLAG,decode
         (INSPECTION_REQUIRED_FLAG||RECEIPT_REQUIRED_FLAG,'NN','2-Way','NY','3-Way','YY','4-Way','Not Specified')  
                              Matching,      
      PLL.ENCUMBERED_FLAG,
      PDA.QUANTITY_ORDERED,    
      PDA.ACCRUAL_ACCOUNT_ID,
      PDA.VARIANCE_ACCOUNT_ID
 FROM
       PO_HEADERS_ALL PHA,
       PO_LINES_ALL PLA,
       PO_LINE_LOCATIONS_ALL PLL,
       PO_DISTRIBUTIONS_ALL PDA
 WHERE
        PDA.PO_HEADER_ID=PLL.PO_HEADER_ID
AND          
       PLL.PO_HEADER_ID=PLA.PO_HEADER_ID
AND
       PLA.PO_HEADER_ID=PDA.PO_HEADER_ID
AND         
       PLA.UNIT_PRICE>1000000000
AND
      decode
(INSPECTION_REQUIRED_FLAG||RECEIPT_REQUIRED_FLAG,'NN','2-Way','NY','3-Way','YY','4-Way','Not Specified')=:MATCH LEVEL


Ur's
AmarAlam

Pending Purchase Orders Report Code

0 comments
select sysdate REP_DATE,
pv.SEGMENT1 VENDOR_NUM,
ph.segment1 PO_NUM,
PH.REVISION_NUM,
ph.type_lookup_code PO_TYPE,
pol.ITEM_DESCRIPTION,
pol.UNIT_MEAS_LOOKUP_CODE,
pol.UNIT_PRICE,hr.name,
pds.QUANTITY_ORDERED,
PDS.QUANTITY_CANCELLED,
pds.QUANTITY_DELIVERED,
pds.QUANTITY_BILLED,
pds.AMOUNT_BILLED,
pol.CANCEL_DATE,
pds.QUANTITY_ORDERED-pds.QUANTITY_DELIVERED REMANING_QTY,
pv.vendor_name Supplier,
PH.AUTHORIZATION_STATUS STATUS,
PH.CREATION_DATE po_date
from po_headers_all ph,
po_lines_all pol,
po_distributions_all pds,
HR_OPERATING_UNITS hr,
po_vendors pv
where ph.PO_HEADER_ID=pol.PO_HEADER_ID
and ph.PO_HEADER_ID=pds.PO_HEADER_ID
and ph.VENDOR_ID=pv.VENDOR_ID
and hr.ORGANIZATION_ID=ph.ORG_ID
AND PdS.QUANTITY_ORDERED<>PdS.QUANTITY_DELIVERED
AND ph.TYPE_LOOKUP_CODE<>'RFQ'
AND ph.TYPE_LOOKUP_CODE<>'QUOTATION'
AND PH.AUTHORIZATION_STATUS='APPROVED'
and hr.NAME=:org
and trunc(ph.creation_date)=trunc(:dt)
and to_char(ph.creation_date,initcap('mon')||'-'||'yy')=:mon
and trunc(ph.creation_date)  between trunc(:inp) - 7   and trunc(:inp)


Ur's
AmarAlam

R12 Fetures and Tables

1 comments
R12 Feaututes :
--------------
1.MOAC(Multiorg Access Control) :
  ------------------------------
  By Using Single Responsibility we can be able to access
  multiple operating units at a time.
2.Mutiorg views were replaced with MOAC Based synonyms.
3.MO:Security Profile is a new profile added in R12.
4.VPD(Virtual Private Database) System will take care of
  Data security in R12

5.Org Initialisation process in R12 :
  ----------------------------------
   begin
     mo_global.set_policy_context('S','204');
   end;

  MOAC Synonym Initialisation :
  ----------------------------
   begin
     FND_GLOBAL.APPS_INTILISE('APPLICATION_ID','RESPONSIBILTY_ID,'USER_ID');
     MO_GLOBAL.INIT('SQLAP'); -- Application Short Name
   end;

  Modulewise Changes in R12 :
  --------------------------
  General Ledger :
  --------------
  1. Subledger Accounting module was added in  R12.
  2. gl_sets_of_books table was replaced with gl_ledgers
     and gl_ledger_Sets.
  3. In Addition to Currency,Calendar,Chart of accounts,
     Convention was added in R12.

   Accounts Payable :
   ----------------
   1.ap_invoice_lines_all table added in R12.
   2.ap_invoice_distributions_all table populates the data
     when ever invoice gets accounted.
   3.Supplier form was converted from form based solution to
     webbased solution.
   4.po_vendors tables replaced with ap_suppliers tables.
   5.Supplier and customer information was defined together
     Under TCA(Trading Community Architecture).
   6.Accounting Tables were modified.
   7.Both supplier and customer bank information was defined
     under payments(New application in R12) Application.
   
      11i Table               R12 Table
      -----------             ------------
      po_vendors              ap_suppliers
      po_vendor_sites_all     ap_supplier_sites_all
      po_vendor_contacts      ap_supplier_contacts
      ap_banks                ce_banks
      ap_bank_branches        ce_bank_branches
      ap_ae_headers_all       xla_ae_headers
      ap_ae_lines_all         xla_ae_lines
      ap_ae_accounting_events xla_events

Order Management  :
-----------------
  Move Order Fautures were added in R12.
  ra_customers ,ra_site_uses_all ,ra_adderesses_all
  views were removed in R12.
  in place of ra_customers,ra_site_uses_all,ra_addersses_all
  views need to use the below mentioned base tables.
 
    hz_cust_accounts
    hz_parties
    hz_cust_site_uses_all
    hz_cust_acct_sites_all
    hz_party_sites
    hz_locations


Ur's
AmarAlam

Important TCA API's

0 comments


Ur's
AmarAlam

Order to Cash Cycle - Tables get Affected @ Each Step

0 comments
1) Order Entry
------------------------------------
oe_order_headers_all.flow_status_code = 'ENTERED'
oe_order_headers_all.booked_flag = 'N'

oe_order_lines_all.flow_status_code = 'ENTERED'
oe_order_lines_all.booked_flag = 'N'
oe_order_lines_all.open_flag = 'Y'

2) Order Booking
------------------------------------
oe_order_headers_all.flow_status_code = 'BOOKED'
oe_order_headers_all.booked_flag = 'Y'

oe_order_lines_all.flow_status_code = 'AWAITING SHIPPING'
oe_order_lines_all.booked_flag = 'Y'

wsh_delivery_details.released_status = 'R' (ready to release)

wsh_delivery_assignments.delivery_id = BLANK

3) Reservation
------------------------------------
mtl_demand
mtl_reservations

4) Pick Release
------------------------------------
wsh_new_deliveries.status_code = 'OP'
wsh_delivery_assignments.delivery_id gets assigned
wsh_delivery_details.released_status = 'S' (submitted for release)
mtl_txn_request_headers
mtl_txn_request_lines
mtl_material_transactions_temp

5) Pick Confirm
------------------------------------
oe_order_lines_all.flow_status_code = 'PICKED'
mtl_material_transactions
wsh_delivery_details.released_status = 'Y' (Released)
mtl_onhand_quantities

6) Ship Confirm
------------------------------------
Data will be removed from wsh_new_deliveries
oe_order_lines_all.flow_status_code = 'SHIPPED'
wsh_delivery_details.released_status = 'C' (Shipped)
wsh_serial_numbers
data will be deleted from mtl_demand and mtl_reservations
item qty gets deducted from mtl_onhand_quantities

7) Enter Invoice
------------------------------------
After shipping the order, workflow backgroud engine picks up records and post it to
ra_interface_lines_all
Auto invoice program picks up records from interface table and insert them into
ra_customer_trx_all (trx_number is invoice number)
ra_customer_trx_lines_all (line_attribute_1 = Order number and line_attribute_6 = Order line id)

8) Complete Line
------------------------------------
oe_order_lines_all.flow_status_code = 'SHIPPED'
oe_order_lines_all.open_flag = 'N'

9) Close Order
------------------------------------
oe_order_headers_all.flow_status_code = 'CLOSED'
oe_order_lines_all.flow_status_code = 'CLOSED'
oe_order_lines_all.open_flag = 'N'

10) Create Receipt
------------------------------------
AR_CASH_RECEIPTS_ALL

11) Transfer to General Ledger
------------------------------------
GL_INTERFACE

12) Journal Import
------------------------------------
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES

13) Posting
------------------------------------
GL_BALANCES


Ur's
AmarAlam

Finding Responsibility of a Form

0 comments

Below is the Query, which helps you to find all the responsibilities of a Form, to which it has been attached.

**********************************************************************************************
SELECT   FORMS.form_name,
         FORMSTL.user_form_name,
         RESTL.responsibility_name,
         FORMSTL.language
  FROM   fnd_form FORMS,
         fnd_form_tl FORMSTL,
         fnd_form_functions FUNC,
         fnd_menu_entries MENU,
         fnd_responsibility RES,
         fnd_responsibility_tl RESTL
 WHERE       FORMSTL.user_form_name LIKE 'CUM Workbench%'
         AND FORMS.form_id = FORMSTL.form_id
         AND FUNC.form_id = FORMS.form_id
         AND MENU.function_id = FUNC.function_id
         AND RES.menu_id(+) = MENU.menu_id
         AND RES.responsibility_id = RESTL.responsibility_id(+)


Ur's
AmarAlam

On-Hand quantity details as per Oracle Form

1 comments
Below is the PL/SQL, which gives you the On-hand Quantity details as per oracle form.

Oracle form show details like On-Hand Quantity, Available to reserve, Quantity Reserved,Quantity Suggested, Available to Transact and Available to Reserve.

All These details can be fetched using API => inv_quantity_tree_pub.query_quantities

****************************************************************************
DECLARE
   x_return_status         VARCHAR2 (50);
   x_msg_count             VARCHAR2 (50);
   x_msg_data              VARCHAR2 (50);
   v_item_id               NUMBER;
   v_organization_id       NUMBER;
   v_qoh                   NUMBER;
   v_rqoh                  NUMBER;
   v_atr                   NUMBER;
   v_att                   NUMBER;
   v_qr                    NUMBER;
   v_qs                    NUMBER;
   v_lot_control_code      BOOLEAN;
   v_serial_control_code   BOOLEAN;
BEGIN
   SELECT   inventory_item_id, mp.organization_id
     INTO   v_item_id, v_organization_id
     FROM   mtl_system_items_b msib, mtl_parameters mp
    WHERE       segment1 = :item_number
            AND msib.organization_id = mp.organization_id
            AND mp.organization_code = :organization_code;


   v_qoh := NULL;
   v_rqoh := NULL;
   v_atr := NULL;
   v_lot_control_code := FALSE;
   v_serial_control_code := FALSE;


   fnd_client_info.set_org_context (1);


   inv_quantity_tree_pub.query_quantities (
      p_api_version_number           => 1.0,
      p_init_msg_lst                        => 'F',
      x_return_status                      => x_return_status,
      x_msg_count                         => x_msg_count,
      x_msg_data                           => x_msg_data,
      p_organization_id                  => v_organization_id,
      p_inventory_item_id              => v_item_id,
      p_tree_mode                         => apps.inv_quantity_tree_pub.g_transaction_mode,
      p_is_revision_control             => FALSE,
      p_is_lot_control                     => v_lot_control_code,
      p_is_serial_control                 => v_serial_control_code,
      p_revision                              => NULL,                          -- p_revision,
      p_lot_number                        => NULL,                        -- p_lot_number,
      p_lot_expiration_date            => SYSDATE,
      p_subinventory_code            => NULL,                 -- p_subinventory_code,
      p_locator_id                         => NULL,                        -- p_locator_id,
      p_onhand_source                 => 3,
      x_qoh                                   => v_qoh,                    -- Quantity on-hand
      x_rqoh                                  => v_rqoh,         --reservable quantity on-hand
      x_qr                                     => v_qr,
      x_qs                                     => v_qs,
      x_att                                     => v_att,               -- available to transact
      x_atr                                    => v_atr                 -- available to reserve
   );


   DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || v_qoh);
   DBMS_OUTPUT.put_line ('Available to reserve: ' || v_atr);
   DBMS_OUTPUT.put_line ('Quantity Reserved: ' || v_qr);
   DBMS_OUTPUT.put_line ('Quantity Suggested: ' || v_qs);
   DBMS_OUTPUT.put_line ('Available to Transact: ' || v_att);
   DBMS_OUTPUT.put_line ('Available to Reserve: ' || v_atr);


EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
END;


****************************************************************************


Ur's
AmarAlam

Query to get Business Group, Legal Entity Name, Operating Unit Details

0 comments
SELECT distinct hrl.country, hroutl_bg.NAME bg, hroutl_bg.organization_id,
       lep.legal_entity_id, lep.NAME legal_entity,
       hroutl_ou.NAME ou_name, hroutl_ou.organization_id org_id,
       hrl.location_id,
       hrl.location_code,
       glev.FLEX_SEGMENT_VALUE
  FROM xle_entity_profiles lep,
       xle_registrations reg,
       hr_locations_all hrl,
       hz_parties hzp,
       fnd_territories_vl ter,
       hr_operating_units hro,
       hr_all_organization_units_tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units gloperatingunitseo,
       gl_legal_entities_bsvs glev
 WHERE lep.transacting_entity_flag = 'Y'
   AND lep.party_id = hzp.party_id
   AND lep.legal_entity_id = reg.source_id
   AND reg.source_table = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id = reg.location_id
   AND reg.identifying_flag = 'Y'
   AND ter.territory_code = hrl.country
   AND lep.legal_entity_id = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id = hro.business_group_id
   AND hroutl_ou.organization_id = hro.organization_id
   AND glev.legal_entity_id = lep.legal_entity_id



Ur's
AmarAlam

Navigation to Add/Edit OM Cancel Reason Codes

0 comments

Using the below navigation in OM, We can add or end date the cancel reasons
Setup -> Quick Codes->Order Management-> CANCEL_CODE


Ur's
AmarAlam

SLA Data Flow and Table Links

0 comments

Ur's
AmarAlam

SQL Query to Link GL Journal and SLA Tables for 'Misc Receipts','Receipts' and 'Reconciled Payments'

3 comments

SELECT  ada.line_id, jh.je_category Category,
  jh.period_name Period_Name,
  NULL Invoice_No_or_Memo_No,
  nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0) Amount_journal,
  NVL(xal.entered_dr,0)-NVL(xal.entered_cr,0) Amount_Xla,
  NVL(ada.amount_dr,0)-NVL(ada.amount_cr,0) Amount_Receivables,
  DECODE(jh.ledger_id,2027,'USD',2029,'EUR',2023,'USD') Currency_Code,
   xdl.source_distribution_type,
  jh.ledger_id Book
FROM gl_je_headers jh,
  gl_je_lines jl,
  gl_code_combinations gcc,
  gl_import_references gir,
  xla_ae_lines xal,
  XLA_DISTRIBUTION_LINKS xdl,
  xla_ae_headers xah,
  AR_DISTRIBUTIONS_ALL ada
WHERE 1                    =1
AND jh.je_header_id        =jl.je_header_id
AND jl.code_combination_id =gcc.code_combination_id
AND gir.je_header_id       =jh.je_header_id
AND gir.je_line_num        =jl.je_line_num
AND gir.gl_sl_link_id      =xal.gl_sl_link_id
and xah.ae_header_id=xal.ae_header_id
and xal.application_id = xdl.application_id
and xah.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num
and xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
and xdl.source_distribution_id_num_1 = ada.line_id
AND gcc.code_combination_id=46032
and jh.period_name like '13-Jun'
and jh.je_category in ('Misc Receipts','Receipts')
and abs(nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0))<> abs(NVL(ada.amount_dr,0)-NVL(ada.amount_cr,0))
and abs(NVL(xal.entered_dr,0)-NVL(xal.entered_cr,0)) <> abs(NVL(ada.amount_dr,0)-NVL(ada.amount_cr,0))
and jh.ledger_id = '2029';



SELECT  jh.name,jh.je_category Category,
  jh.period_name Period_Name,
  NULL Invoice_No_or_Memo_No,
  abs(nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0)) Amount_journal,
  abs (NVL(xal.accounted_dr,0)-NVL(xal.accounted_cr,0)) Amount_Xla,
  abs(sum(aphd.amount)) amount_payment,
    DECODE(jh.ledger_id,2027,'USD',2029,'EUR',2023,'USD') Currency_Code,
   xdl.source_distribution_type,
  jh.ledger_id Book
FROM gl_je_headers jh,
  gl_je_lines jl,
  gl_code_combinations gcc,
  gl_import_references gir,
  xla_ae_lines xal,
  XLA_DISTRIBUTION_LINKS xdl,
  xla_ae_headers xah,
  AP_PAYMENT_HIST_DISTS aphd
WHERE 1                    =1
AND jh.je_header_id        =jl.je_header_id
AND jl.code_combination_id =gcc.code_combination_id
AND gir.je_header_id       =jh.je_header_id
AND gir.je_line_num        =jl.je_line_num
AND gir.gl_sl_link_id      =xal.gl_sl_link_id
and xah.ae_header_id=xal.ae_header_id
and xal.application_id = xdl.application_id
and xah.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num
AND gcc.code_combination_id=46032
and xdl.source_distribution_type = 'AP_PMT_DIST'
and xdl.source_distribution_id_num_1 = aphd.payment_hist_dist_id
and jh.je_category in ('Reconciled Payments')
and jh.ledger_id ='2029'
and jh.period_name like '13-Jun'
--and abs (nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0))  <> abs(sum(aphd.amount) )
--and NVL(xal.accounted_dr,0)-NVL(xal.accounted_cr,0) <> abs(sum(aphd.amount)),
 group by  jh.name,jh.je_category,
  jh.period_name,
   nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0),
  NVL(xal.accounted_dr,0)-NVL(xal.accounted_cr,0),
     DECODE(jh.ledger_id,2027,'USD',2029,'EUR',2023,'USD'),
   xdl.source_distribution_type,
  jh.ledger_id
  having --abs (nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0))  <> abs(sum(aphd.amount) );
abs( NVL(xal.accounted_dr,0)-NVL(xal.accounted_cr,0)) <> abs(sum(aphd.amount));



Ur's
AmarAlam

Query to find KEY FLEX FIELDS (KFF)

0 comments
Query to find KEY FLEX FIELDS (KFF)

select  FIF.APPLICATION_ID  ,
        FIF.ID_FLEX_CODE    ,
        FIF.ID_FLEX_NAME    ,
        FIF.APPLICATION_TABLE_NAME ,
        FIF.DESCRIPTION     ,
        FIFS.ID_FLEX_NUM    ,
        FIFS.ID_FLEX_STRUCTURE_CODE  ,
        FIFSE.SEGMENT_NAME,
        FIFSE.SEGMENT_NUM,
        FIFSE.FLEX_VALUE_SET_ID
from    FND_ID_FLEXS FIF    ,
        FND_ID_FLEX_STRUCTURES FIFS ,
        FND_ID_FLEX_SEGMENTS FIFSE
where   FIF.APPLICATION_ID = FIFS.APPLICATION_ID
and     FIF.ID_FLEX_CODE   = FIFS.ID_FLEX_CODE
and     FIFSE.APPLICATION_ID = FIF.APPLICATION_ID
and     FIFSE.ID_FLEX_CODE = FIF.ID_FLEX_CODE
and     FIFSE.ID_FLEX_NUM = FIFS.ID_FLEX_NUM
and     FIF.ID_FLEX_CODE LIKE 'GL#'
and     FIF.ID_FLEX_NAME LIKE 'Accounting Flexfield';


Ur's
AmarAlam

D2K Reports Basics

0 comments
D2K Reports Basics:

Introduction to Oracle Reports Builder:

Oracle Reports Builder is a powerful enterprise reporting tool used to build reports that dynamically retrieve data from the database, format, display and print quality reports. Reports can be stored in File or Database (Report Builder Tables).

Report file storage formats:

.rdf Report :

• Binary File Full report definition (includes source code and comments)
• Modifiable through Builder. Binary, executable Portable if transferred as binary.
• PL/SQL recompiles on Open/Run

.rep Report :

• Binary Run-Only File
• No source code or comments. Not modifiable binary, executable.
• Report Executables

You can use the Reports File Converter (rwcon60) to convert a .RDF to a .REP file. RWCON60 Report Converter/Compiler [File => Administration => Compile (rdf torep)/Convert]

Oracle Reports Builder Tools:

Oracle Reports Builder comes with the following components

• Object Navigator
• Property Palette
• Data Model Editor
• Layout Model Editor
• Parameter Form Editor

Object Navigator:
The Object Navigator shows a hierarchical view of objects in the report. Each item listed is called a node and represents an Object or type of object the report can contain or reference.

Property Palette:
A Property Palette is a window that displays the settings for defining an Oracle reports object.

Data Model Editor:
To specify data for a report, a data model should be defined. A data model is composed of some or all of the following data definition objects.

Queries:
Queries are SQL Select statements that fetch data from the oracle database.
These statements are fired each time the report is run.

Groups:
Groups determine the hierarchy of data appearing in the report and are primarily used to group columns selected in the query. Oracle report automatically creates a group for each query.

Data Columns:
Data columns contain the data values for a report. Default data columns,corresponding to the table columns included in each query’s SELECT list are automatically created by oracle reports. Each column is placed in the group associated with the query that selected the column.

Formula Columns:
Formulas can be entered in formula columns to create computed columns.
Formulas can be written using PL/SQL syntax. Formula columns are generally preceded by CF_ to distinguish from other columns.

Summary Columns:
Summary columns are used for calculating summary information like sum, average etc. This column uses a set of predefined oracle aggregate functions.

Summary columns are generally preceded by CS_ to distinguish them from other columns.

Placeholder Column:
Place holder column is the name of variable which can hold a calculated value like (sum, avg..) or the value can be set by function or by a pl/sql block. Use of place holder column for aggrigate function is not advised, for that use summary column.

Data Links:
Data links are used to establish parent-child relationships between queries and groups via column matching.

Layout Model Editor:
A report layout editor contains the following layout objects

Frames:
Frames surround other layout objects, enabling control of multiple objects simultaneously

Repeating Frames:
Repeating frames acts as placeholders for groups (I.e repeating values) and present rows of data retrieved from the database. Repeating frames repeat as often as the number of rows retrieved.

Fields:
Fields acts as placeholders for columns values. They define the formatting attributes for all columns displayed in the report.

Boilerplate:
Boilerplate consists of text (label of the column) and graphics that appear in a report each time it is run.

Anchor:
Anchors fasten an edge of one object to an edge of another object, ensuring that they maintain their relative positions.

Parameter Form Editor:
Parameter form is a run time form used to accept inputs from the user.

Parameters:
Parameters are variables for a report that accept input from the user at run time. These parameter values can then be used in the SQL select statements to retrieve data conditionally. Oracle reports creates a set of system parameters at run time namely report destination type, number of copies etc.

Report Wizard:

• When we create a default Tabular Report using report wizard, the wizard will take you through the below mentioned pages
• Report Style Tabular, Form-Like, Mailing Label, Form Letter, Group Left, Group

Above, Matrix, Matrix with Group

• Query Type Choose whether to build a SQL query or an Express query.
• Data Enter a SELECT statement to retrieve the report data.
• Displayed Fields Select the fields that you want to display in the output.
• Fields to Total Select the fields that you want to summarize.
• Labels for Fields Alter the labels that appear for each field and the width of each field.
• Template Select the template that you want to use for this report. A template contains standard information such as company logo, date, and so on.

Note: The above steps are different for each report style.

Group Left & Have an additional page: ‘Groups’

Group Above styles

Matrix Reports styles Have 3 additional pages:
 ‘Matrix Rows’ ‘Columns’ ‘Cells’ Mailing Label & Have 4 pages: ‘Report Style’ ‘Data’
Form Letter styles ‘Text’ ‘Template’

The difference between Mailing Labels and Form Letters is, Mailing Label shows multiple records on one page while Form Letter shows one record on each page.

Triggers in Reports:

Types of Triggers:

Formula Triggers: Formula triggers are PL/SQL functions that populate columns of type Formula.

Format Triggers: Format triggers are PL/SQL functions executed before the object is formatted. These triggers are used to dynamically change the formatting attributes and used to conditionally print and not to print a report column value. These triggers return Boolean values TRUE or FALSE. If the return value of the format trigger is FALSE, the value is not displayed.

Action Triggers: Action triggers are used to perform user-defined action. These triggers do not return any value.

Validation Triggers: Validation triggers are PL/SQL functions that are executed when a parameter value is entered and the cursor moves to the next parameter. These triggers return Boolean value TRUE / FALSE.

Report Triggers: Report triggers enable execution of PL/SQL functions at specific time during execution and formatting of report.

Trigger Firing Sequence:

Before Parameter :
Fires before the Runtime Parameter Form are displayed. Can access the PL/SQL
global variables, report level columns and manipulate accordingly.

After Parameter:
Fires after the Runtime Parameter form are displayed. Used to validate the parameter values.

Before Report:
Fires before the report is executed but after the queries is parsed and date is fetched.

Between Pages:
Fires before each page of the report are formatted, except the very first page.
This page is used to customize page formatting.

After Report:
Fires after the report previewer are exited, or after report output is sent to a specified destination.


Ur's
AmarAlam

Thursday, 22 August 2013

Query to know path of log file and out file of a concurrent program

0 comments
SELECT logfile_name, outfile_name
FROM fnd_concurrent_requests
WHERE request_id = 408408

Ur's
AmarAlam

How to select value of profile through query

0 comments
=================================
select fnd_profile.VALUE ( 'XXAJ_ALAM_TEST_SERVER_URL' )
into v_var
from dual;

or use this in plsql block :

v_var1 := fnd_profile.VALUE ( 'XXAJ_ALAM_TEST_SERVER_URL' );
=================================

Ur's
AmarAlam

Code for attaching request set to request group

0 comments
set serveroutput on
column date_column new_value today_var
select to_char(sysdate,'YYYYMMDDHHMI') date_column from dual
/
--
spool XXAJ_PO_APPROVAL_DETAILS_DATA
--
BEGIN
-- Add Request Set to request group.
BEGIN 
fnd_set.add_set_to_group (request_set => 'XXAJ_PO_APPROVAL_DETAILS',
set_application => 'Order Management',
request_group => 'ALL Reports',
group_application => 'ONT'
);
DBMS_OUTPUT.PUT_LINE ('"XXAJ_PO_APPROVAL_DETAILS" attached to request group uccessfully ');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Error in attaching "XXAJ_PO_APPROVAL_DETAILS" to equest group ' || SQLERRM);
END;
--
COMMIT;


Ur's
AmarAlam

AIM Documents

3 comments


Ur's
AmarAlam

Supplier-Invoice-Payment Query in Oracle Apps

0 comments
The following query gives the following

1. Requisition Detail
2. Purchase Order Details
3. Receiving Details
4. Invoicing Detail
5. Payment Details

Just modify it to query only the invoice and payment part.

Code sql:

SELECT   A.ORG_ID "ORG ID",
         E.VENDOR_NAME "VENDOR NAME",
         UPPER (E.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE",
         F.VENDOR_SITE_CODE "VENDOR SITE",
         F.ADDRESS_LINE1 "ADDRESS",
         F.CITY "CITY",
         F.COUNTRY "COUNTRY",
         TO_CHAR (TRUNC (D.CREATION_DATE)) "PO DATE",
         D.SEGMENT1 "PO NUMBER",
         D.TYPE_LOOKUP_CODE "PO TYPE",
         C.QUANTITY_ORDERED "QTY ORDERED",
         C.QUANTITY_CANCELLED "QTY CANCALLED",
         G.ITEM_DESCRIPTION "ITEM DESCRIPTION",
         G.UNIT_PRICE "UNIT PRICE",
         (NVL (C.QUANTITY_ORDERED, 0) - NVL (C.QUANTITY_CANCELLED, 0))
         * NVL (G.UNIT_PRICE, 0)
            "PO Line Amount",
         (SELECT   DECODE (PH.APPROVED_FLAG, 'Y', 'Approved')
            FROM   PO.PO_HEADERS_ALL PH
           WHERE   PH.PO_HEADER_ID = D.PO_HEADER_ID)
            "PO STATUS",
         A.INVOICE_TYPE_LOOKUP_CODE "INVOICE TYPE",
         A.INVOICE_AMOUNT "INVOICE AMOUNT",
         TO_CHAR (TRUNC (A.INVOICE_DATE)) "INVOICE DATE",
         A.INVOICE_NUM "INVOICE NUMBER",
         (SELECT   DECODE (X.MATCH_STATUS_FLAG, 'A', 'Approved')
            FROM   AP.AP_INVOICE_DISTRIBUTIONS_ALL X
           WHERE   X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)
            "Invoice Approved?",
         A.AMOUNT_PAID,
         H.AMOUNT,
         I.CHECK_NUMBER "CHEQUE NUMBER",
         TO_CHAR (TRUNC (I.CHECK_DATE)) "PAYMENT DATE"
  FROM   AP.AP_INVOICES_ALL A,
         AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
         PO.PO_DISTRIBUTIONS_ALL C,
         PO.PO_HEADERS_ALL D,
         PO.PO_VENDORS E,
         PO.PO_VENDOR_SITES_ALL F,
         PO.PO_LINES_ALL G,
         AP.AP_INVOICE_PAYMENTS_ALL H,
         AP.AP_CHECKS_ALL I
 WHERE       A.INVOICE_ID = B.INVOICE_ID
         AND B.PO_DISTRIBUTION_ID = C.PO_DISTRIBUTION_ID(+)
         AND C.PO_HEADER_ID = D.PO_HEADER_ID(+)
         AND E.VENDOR_ID(+) = D.VENDOR_ID
         AND F.VENDOR_SITE_ID(+) = D.VENDOR_SITE_ID
         AND D.PO_HEADER_ID = G.PO_HEADER_ID
         AND C.PO_LINE_ID = G.PO_LINE_ID
         AND A.INVOICE_ID = H.INVOICE_ID
         AND H.CHECK_ID = I.CHECK_ID
         AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
         AND C.PO_HEADER_ID IS NOT NULL
         AND A.PAYMENT_STATUS_FLAG = 'Y'
         AND D.TYPE_LOOKUP_CODE != 'BLANKET';


Ur's
AmarAlam

Wednesday, 14 August 2013

Important questions to be remembered by a Oracle Consultant

0 comments
What is Set of Books ? What are the four conditions when you change your SOBs?

Chart of Accounts, Currency & Calendar
It is similar to the bank passbook, used to record all the financial transactions. There could be one primary set of books and many reporting set of books. Defining COA, currency and calendar are pre-requisite to define the SOB.

What is an Invoice? How many types of invoices AR...
There are 9 types of Invoices in AP: Standard, Credit Memo, Debit Memo, Prepayment, Withholding Tax, Mixed Invoice, Expense Report, PO default and Quick Match.
There are 6 types of Invoice in AR : Invoice, Credit Memo, Debit Memo, Chargeback, Deposit and Guarantee.

What is the difference between data conversion and data migration?
Data Migration is upgrading from one version to another version fro e.g. from 11.5.9 to 11.5.10.. Data Conversion is receiving data from a legacy system or receiving data from another module ( e.g. PA), validating it and converting it to ur our the target module using the standard import program.

Set ups need to approve invoice in AP
For approving an invoice in AP we have to raise one invoice for raising an invoice we have some mandatory fields like supplier, supplier number, Payment terms, distribution sets, date, payment method, bank, payment document etc. After defining all the above we can raise a invoice, There's no need of defining the approval group it s not a mandatory,

What is a profile option? What are the types?
The profile options are available to every product in Oracle Applications. For each profile option, we give a brief overview of how Oracle Application Object Library uses the profile's setting.
1) User Level
2) Responsibility Level
3) Application Level
4) Site Level.

What are _ALL tables in Oracle Apps?
_ALL tables in oracle applications give the info about multiple organizations info about these tables.

What is descriptive flex field and what is the use
Descriptive Flexfileds r used to add additional information's, and these values r stored to the column attributes. Go to sysadmin application-flex fields-descriptive-segments.

What is a FlexField? What are Descriptive and Key...
A flexfield is made up of sub-fields or segments.. A flexfield appers on ur form as a pop-up window that contains a prompt for each segment. Each segment has a name and a set of valid values..
Two types of Flex field..
Key Flexfield: Key flexfield are flexible enough to let any organization use the code scheme they want without programming.Key flexfield can be used to represent codes that is made up of meaningful segment to identify GL a/c Part no. and other business entities..Oracle app store these codes in key flexfields..
Descriptive Flexfield: They provide customization "expansion space" on ur forms. You can use desc flexfields to tract additional information important and unique to ur business that would not otherwise be captured by the form.

What Credit memo / Debit Memo
Both Credit and Debit Memo are used for adjusting the suppliers balance and both is a negative amount. Debit Memo is created by you and send to the supplier and credit memo is received from the supplier and record it.

What is a request Sets? how to create a request Se..
Request set is a group of requests.It is made to perform the request in a certain sequence.Request se can be created from System administrator responsibility.

What are the types of Concurrent Managers?Ca...
There are many concurrent managers, each monitoring the flow within each apps area.
but there are 3 MASTER CONCURRENT MANAGERS:
1. Internal Conccurent Manager (ICM): This is the one which monitors all other CMs
2. Standard Manager (SM) : This takes care of report running and batch jobs
3. Conflict Resolution Manager (CRM): checks concurrent program definitions for incompatibility checks.
We cannot delete a concurrent manager... but we can disable it... but it's not recommended.

What is Multiple Organization technical architecture?
Multi Organization :Using a single installation of any oracle application product , to support any number of an organization even it has different Set Of Books Business Level(It Secures Human Transaction Information) Set Of Books(It secures all Transaction Information In Oracle General Ledger) Legal Entry(All legal Information in the Organization) Operating Unit(It Uses Order management, Cash management,AR,AP...it may be Sales Office , Division,Department) Inventory Organization(Inventory Details)
before multi org existed, we could have only one business group,set of books, legal entity, operating unit in one installation of oracle E-business suite, and now with the multi organisation structure in place, we can have multiple business groups, set of books, legal entity and operating unit and the best part is once these are set up inter company accounting is automatically taken care of, say for example i have Two inventory organisation, and these use a common set of books(to start with) , now if we have sales order on one inventory organisation A1 and if that item is not available in A1, and we have inventory for the item in Inventory org A2, we need to take the order in A1 as internal order and run the order import concurrent program and for the item in inventory A1(org assignment) we need to set up the source(purchase tab) as inventory organisation A2,and also specify shipping network between A1 and A2, and once we have run the order import program and Oracle applications now imports the order to organisation A2, with the ship to address as A1 location. and after you perform pick release and pick confirm process, and run auto invoice in A2, it automatically sends invoice to inventory organisation A1 and now we can receive the item in Inventory organisation A1 from organisation A2.
and all transactions are taken care of, courtesy multi organisation structure.

What are different period types ?
Year Quarter Month Week

What are the different types of files used in SQL
Different types of files are Data File,Control File,Discard File,Bad file

What is Oracle Financials?
Oracle Financials products provide organizations with solutions to a wide range of long- and short-term accounting system issues. Regardless of the size of the business, Oracle Financials can meet accounting management demands with:
o Oracle Assets: Ensures that an organization's property and equipment investment is accurate and that the correct asset tax accounting strategies are chosen.
o Oracle General Ledger: Offers a complete solution to journal entry, budgeting, allocations, consolidation, and financial reporting needs.
o Oracle Inventory: Helps an organization make better inventory decisions by minimizing stock and maximizing cash flow.
o Oracle Order Entry: Provides organizations with a sophisticated order entry system for managing customer commitments.
o Oracle Payable's  Lets an organization process more invoices with fewer staff members and tighter controls. Helps save money through maximum discounts, bank float, and prevention of duplicate payment.
o Oracle Personnel: Improves the management of employee- related issues by retaining and making available every form of personnel data.
o Oracle Purchasing: Improves buying power, helps negotiate bigger discounts, eliminates paper flow, increases financial controls, and increases productivity.
o Oracle Receivables:. Improves cash flow by letting an organization process more payments faster, without off-line research. Helps correctly account for cash, reduce outstanding receivables, and improve collection effectiveness.
o Oracle Revenue Accounting: Gives an organization timely and accurate revenue and flexible commissions reporting.
o Oracle Sales Analysis: Allows for better forecasting, planning. and reporting of sales information.

What is the difference between Fields and FlexFields?
A field is a position on a form that one uses to enter, view, update, or delete information. A field prompt describes each field by telling what kind of information appears in the field, or alternatively, what kind of information should be entered in the field.
A flexfield is an Oracle Applications field made up of segments. Each segment has an assigned name and a set of valid values. Oracle Applications uses flexfields to capture information about your organization. There are two types of flexfields: key flexfields and descriptive flexfields.

Difference between Conversions and Interfaces.
Conversion is to bring the data from other (non-OraApps) system to Oracle-Application system. This is one time activity (bring account details, transactions, orders, receipts and so on). So conversion itself is a project.
Interfaces are Concurrent Program's/Program sets (pl/sql, pro*C, unix scripts, executable's) . Interfaces are basically of two types (Oracle supplied (vanilla programs eg: Auto invoice in AR) and created by developer).
Hope this clarifies the concepts.
Conversion means one time activity interface means periodic activity example:- to transfer the data old version to new version it is called conversion to transfer the data from staging table to interface table it is called interface , it is process on every day or every hour ........

Which module is not a multiorg? 
General Ledger and CRM Foundation Modules

What are the types of Concurrent Managers?Can we delete a Concurrent Manager?
There are many concurrent managers, each monitoring the flow within each apps area.
but there are 3 MASTER CONCURRENT MANAGERS:
1. Internal Conccurent Manager (ICM): This is the one which monitors all other CMs
2. Standard Manager (SM) : This takes care of report running and batch jobs
3. Conflict Resolution Manager (CRM): checks concurrent program definitions for incompatability checks.
We cannot delete a concurrent manager... but we can disable it... but it's not recommended.

Trading community Architecture(TCA)
ICA (Internet Computing Architecture)

What are AP setup steps ?
setup---->suppliers....>invoices..>payments.....>reports......>periods.....>transfer to GL

What does US mean in appl_top/au/11.5.0/reports/US?
US is the language directory specifying that the source files to be placed under this dir is for English/American Language
This is the standard of apps directory structure that for very language you implement oracle apps there should be a language specific folder

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers? 
SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

What are the Back ground processes in Oracle and what are they.
There are basically 9 Processes.They do the house keeping activities for the Oracle and are common in any system.The various background processes in oracle are:
a) Data Base Writer(DBWR) :: Data Base Writer Writes Modified blocks from Database buffer cache to Data Files.This is required since the data is not written whenever a transaction is committed.
b)LogWriter(LGWR) :: Log Writer writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online redo log file.
c) System Monitor(SMON) :: The System Monitor performs instance recovery at instance start up. This is useful for recovery from system failure
d)Process Monitor(PMON) :: The Process Monitor performs process recovery when user Process fails. Pmon Clears and Frees resources that process was using.
e) CheckPoint(CKPT) :: At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the
most recent checkpoint
f)Archieves(ARCH) :: The Archives copies online redo log files to archival storal when they are busy.
g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed transaction in network
h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecture
i) Lckn :: We can have up to 10 lock processes for inter instance locking in parallel sql.

How many types of Sql Statements are there in Oracle
There are basically 6 types of sql statments.They are
a) Data Definition Language(DDL) :: The DDL statements define and maintain objects and drop objects.
b) Data Manipulation Language(DML) :: The DML statements manipulate database data.
c) Transaction Control Statements :: Manage change by DML
d) Session Control :: Used to control the properties of current session enabling and disabling roles and changing .e.g, Alter Statements,Set Role
e) System Control Statements :: Change Properties of Oracle Instance .e.g, Alter System
f) Embedded Sql :: Incorporate DDL,DML and T.C.S in Programming Language.e.g, Using the Sql Statements in languages such as 'C', Open,Fetch, execute and close

What is a Transaction in Oracle
A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statment and ends when it is explicitly committed or rolled back.

Key Words Used in Oracle
The Key words that are used in Oracle are ::
a) Committing :: A transaction is said to be committed when the transaction makes permanent changes resulting from the SQL statements.
b) Rollback :: A transaction that retracts any of the changes resulting from SQL statements in Transaction.
c) Save Point :: For long transactions that contain many SQL statements, intermediate markers or savepoints are declared. Save points can be used to divide a transaction into smaller points.
d) Rolling Forward :: Process of applying redo log during recovery is called rolling forward.
e) Cursor :: A cursor is a handle ( name or a pointer) for the memory associated with a specific statement. A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explicit cursor for a multi row query.
f) System Global Area(SGA) :: The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance.It consists of Database Buffer Cache and Redo log Buffer.
g) Program Global Area (PGA) :: The PGA is a memory buffer that contains data and control information for server process.
g) Database Buffer Cache :: Database Buffer of SGA stores the most recently used blocks of database data.The set of database buffers in an instance is called Database Buffer Cache.
h) Redo log Buffer :: Redo log Buffer of SGA stores all the redo log entries.
i) Redo Log Files :: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.
j) Process :: A Process is a 'thread of control' or mechanism in Operating System that executes series of steps.

What are Procedure,functions and Packages
* Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.
* Procedures do not Return values while Functions return one One Value
*Packages :: Packages Provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents

What are Database Triggers and Stored Procedures
Database Triggers :: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table. Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which cannot be enforced using the integrity rules.We can have the trigger as Before trigger or After Trigger and at Statement or Row level.
e.g:: operations insert,update ,delete 3
before ,after 3*2 A total of 6 combinations
At statement level(once for the trigger) or row level( for every execution ) 6 * 2 A total of 12.
Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 On wards.
Stored Procedures :: Stored Procedures are Procedures that are stored in Compiled form in the database.The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.

How many Integrity Rules are there and what are they
There are Three Integrity Rules. They are as follows ::
a) Entity Integrity Rule :: The Entity Integrity Rule enforces that the Primary key cannot be Null
b) Foreign Key Integrity Rule :: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced.When there is data in Child Tables the Master tables cannot be deleted.
c) Business Integrity Rules :: The Third Integrity rule is about the complex business processes which cannot be implemented by the above 2 rules.

What are the Various Master and Detail Relation ships.
The various Master and Detail Relationship are
a) NonIsolated :: The Master cannot be deleted when a child is exisiting
b) Isolated :: The Master can be deleted when the child is existing.
c) Cascading :: The child gets deleted when the Master is deleted.

What are the Various Block Coordination Properties
The various Block Coordination Properties are
a) Immediate
Default Setting. The Detail records are shown when the Master Record are shown.
b) Deffered with Auto Query
Oracle Forms defer fetching the detail records until the operator navigates to the detail block.
c) Deffered with No Auto Query
The operator must navigate to the detail block and explicitly execute a query

What are the Different Optimization Techniques
The Various Optimization techniques are
a) Execute Plan :: we can see the plan of the query and change it accordingly based on the indexes
b) Optimizer_hint ::
set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS');
Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from dept
where (Deptno > 25)
c) Optimize_Sql ::
By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements.This slow downs the processing because for evertime the SQL must be parsed whenever they are executed.
f45run module = my_firstform userid = scott/tiger optimize_sql = No
d) Optimize_Tp ::
By setting the Optimize_Tp= No, Oracle Forms assigns seperate cursor only for each query SELECT statement. All other SQL statements reuse the cursor.
f45run module = my_firstform userid = scott/tiger optimize_Tp = No

Restrict '&' in Sql*Plus
sql> set define off
This will restrict the use of '&' in the sql*plus for that particular session


Ur's
Amar Alam

Some Interesting SQL Queries

0 comments
Query to retrieve 1,4,7,10.....rows from a table

SELECT a.* FROM emp a WHERE (ROWID, 1) IN (SELECT ROWID, MOD (ROWNUM, 3) FROM emp);

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

Query to print Rupees in words :

SELECT sal "Salary ", (' Rs. ' (TO_CHAR (TO_DATE (sal, 'j'), 'Jsp')) ' only.' ) "Sal in Words" FROM emp

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

Query to print the calender for the year

SELECT LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH, "Sun", "Mon",
"Tue", "Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
TO_CHAR (dt + 1, 'iw') week,
MAX (DECODE (TO_CHAR (dt, 'd'),
'1', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Sun",
MAX (DECODE (TO_CHAR (dt, 'd'),
'2', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Mon",
MAX (DECODE (TO_CHAR (dt, 'd'),
'3', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Tue",
MAX (DECODE (TO_CHAR (dt, 'd'),
'4', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Wed",
MAX (DECODE (TO_CHAR (dt, 'd'),
'5', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Thu",
MAX (DECODE (TO_CHAR (dt, 'd'),
'6', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Fri",
MAX (DECODE (TO_CHAR (dt, 'd'),
'7', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Sat"
FROM (SELECT TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12) - TRUNC (SYSDATE, 'y')) GROUP BY TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw'))
ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week)

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

Use of Order by clause for records with datatype as character

We generally cannot order the records for a column with character data type. Please use the following trick to use the order clause:

select employee_number from employees
order by lpad(employee_number,100);

Using the lpad the zeros would be appended and then sql will treat them as numbers and the employee number would be sorted.

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

Printing Fibonacci series in PL/SQL


DECLARE
RESULT NUMBER := 1;
previous NUMBER := -1;
l_sum NUMBER;
n NUMBER;
l_in NUMBER := 10;
BEGIN
FOR n IN 1 .. l_in
LOOP
l_sum := RESULT + previous;
previous := RESULT;
RESULT := l_sum;
DBMS_OUTPUT.put_line (l_sum);
END LOOP;
END;

Other ways to print the Fib series is as follows:

CREATE OR REPLACE FUNCTION fib (n POSITIVE) RETURN INTEGER IS
BEGIN
IF (n = 1) OR (n = 2) THEN -- terminating condition
RETURN 1;
ELSE
RETURN fib(n - 1) + fib(n - 2); -- recursive call
END IF;
END fib;

/

-- Test Fibonacci Series:

SELECT fib(1), fib(2), fib(3), fib(4), fib(5) FROM dual;


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

Demonstrate simple encoding and decoding of secret messages 

SELECT TRANSLATE(
'HELLO WORLD', -- Message to encode
'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
'1234567890!@#$%^&*()-=_+;,.') ENCODED_MESSAGE
FROM DUAL
/

SELECT TRANSLATE(
'85@@%._%*@4', -- Message to decode
'1234567890!@#$%^&*()-=_+;,.',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ ') DECODED_MESSAGE
FROM DUAL
/

Computing the Factorial of a number (n!)

CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN INTEGER IS
BEGIN
IF n = 1 THEN -- terminating condition
RETURN 1;
ELSE
RETURN n * fac(n - 1); -- recursive call
END IF;
END fac;
/

-- Test n!

SELECT fac(1), fac(2), fac(3), fac(4), fac(5) FROM dual;

sql to print string vertically

SELECT SUBSTR ('&&String', ROWNUM, 1)
FROM all_tables
WHERE ROWNUM <= LENGTH (TRIM ('&STRING'));

Procedure to reverse a string

SQL> create or replace procedure rev(x in varchar2) as
2 c char(1);
3 i number;
4 begin
5 for i in 1..length(x) loop
6 select substr(x,length(x)-i+1,1) into c from dual;
7 dbms_output.put(c);
8 end loop;
9 dbms_output.put_line(' ');
10 end;
11 /

SQL> set serverout on
SQL> exec rev('Amar Alam')
           malA ramA

Display the PL/SQL Dependency Tree

SELECT lvl, u.object_id, u.object_type, LPAD (' ', lvl) || object_name obj
FROM (SELECT LEVEL lvl, object_id
FROM SYS.public_dependency s
START WITH s.object_id =
(SELECT object_id
FROM user_objects
WHERE object_name = UPPER ('&OBJECT_NAME')
AND object_type = UPPER ('&OBJECT_TYPE'))
CONNECT BY s.object_id = PRIOR referenced_object_id
GROUP BY LEVEL, object_id) tree,
user_objects u
WHERE tree.object_id = u.object_id
ORDER BY lvl

When prompted, enter the OBJECT_NAME of the object whose dependencies you want to identify.

The OBJECT_NAME can be a PACKAGE, PACKAGE BODY, or PROCEDURE. The OBJECT_NAME is the name of the object at the root of the tree.

SQL script to lists all the profile settings (all levels)
SELECT pot.user_profile_option_name "Profile"
, DECODE( a.profile_option_value
, '1', '1 (may be "Yes")'
, '2', '2 (may be "No")'
, a.profile_option_value) "Value"
, DECODE( a.level_id
, 10001, 'Site'
, 10002, 'Appl'
, 10003, 'Resp'
, 10004, 'User'
, '????') "Levl"
, DECODE( a.level_id
, 10002, e.application_name
, 10003, c.responsibility_name
, 10004, d.user_name
, '-') "Location"
FROM applsys.fnd_application_tl e
, applsys.fnd_user d , applsys.fnd_responsibility_tl c
, applsys.fnd_profile_option_values a , applsys.fnd_profile_options b
, applsys.fnd_profile_options_tl pot
WHERE UPPER( pot.user_profile_option_name) LIKE UPPER( '%&&v_profile%')
AND pot.profile_option_name = b.profile_option_name
AND b.application_id = a.application_id (+)
AND b.profile_option_id = a.profile_option_id (+)
AND a.level_value = c.responsibility_id (+)
AND a.level_value = d.user_id (+) AND a.level_value = e.application_id
(+)
AND( UPPER( e.application_name) LIKE UPPER( '%&&v_username%')
OR UPPER( c.responsibility_name) LIKE UPPER( '%&&v_username%')
OR UPPER( d.user_name) LIKE UPPER( '%&&v_username%'))
ORDER BY "Profile", "Levl", "Location", "Value"



Ur's
Amar Alam

Interface Table Names in Oracle Apps

3 comments
oracle financials – payables - invoices 
ap_invoices_interface
ap_invoice_lines_interface

oracle financials – receivables - customers 
ra_customers_interface_all
ra_customer_profiles_int_all
ra_contact_phones_int_all
ra_customer_banks_int_all
ra_cust_pay_method_int_all
hz_party_interface

oracle financials – receivables - invoices 
ra_interface_lines_all
ra_interface_distributions_all
ra_interface_salescredits_all

oracle financials – receivables – lock box 
ar_payments_interface_all

oracle financials – receivables - tax 
ar_tax_interface

oracle financials - cash management – bank statements 
ce_statement_headers_int_all
ce_statement_lines_interface

oracle financials - fixed assets 
fa_inv_interface

oracle financials - general ledger – journal entry 
gl_interface

oracle manufacturing – cost management 
cst_pc_item_cost_interface
cst_pc_cost_det_interface

oracle manufacturing - inventory 
mtl_replenish_headers_int
mtl_replenish_lines_int
mtl_serial_numbers_interface
mtl_system_items_interface
mtl_transaction_lots_interface
mtl_transactions_interface

oracle manufacturing – purchasing – purchase orders 
po_headers_interface
po_lines_interface
po_distributions_interface
po_requisitions_interface_all
po_req_dist_interface_all
po_reschedule_interface

oracle manufacturing – purchasing - receiving 
rcv_headers_interface
rcv_transactions_interface
rcv_lots_interface
rcv_serials_interface

oracle manufacturing - order entry – sales orders 
so_headers_interface_all
so_header_attributes_interface
so_lines_interface_all
so_line_attributes_interface
so_line_details_interface
so_price_adjustments_interface
so_sales_credits_interface

oracle manufacturing - master scheduling/mrp 
mrp_forecast_interface
mrp_schedule_interface

oracle manufacturing - shop floor management 
wsm_lot_job_interface
wsm_starting_lots_interface
wsm_lot_move_txn_interface

oracle manufacturing - quality 
qa_results_interface

oracle manufacturing - work in process 
wip_move_txn_interface
cst_comp_snap_interface
wip_cost_txn_interface
wip_job_schedule_interface
wip_job_dtls_interface


Ur's
Amar Alam

Oracle Receivables Module Technical Details

0 comments
Oracle Accounts Receivable uses the following tables for recording customer account information:

The major tables containing parties and customer accounts information in Oracle Receivables are grouped by business function.
• HZ_PARTIES
• HZ_CUST_ACCOUNTS
• HZ_PARTY_SITES
• HZ_CUST_ACCT_SITES_ALL
• HZ_CUST_SITE_USES_ALL
• HZ_LOCATIONS
• HZ_PARTY_RELATIONSHIPS
• HZ_ORGANIZATION_PROFILES
• HZ_CONTACT_POINTS
• HZ_CUST_ACCOUNT_ROLES
• HZ_PERSON_PROFILES
• HZ_ORG_CONTACTS

Transaction Tables
• RA_CUSTOMER_TRX_ALL
• RA_CUSTOMER_TRX_LINES_ALL
• RA_CUST_TRX_LINE_GL_DIST_ALL
• AR_PAYMENT_SCHEDULES_ALL
• AR_CASH_RECEIPTS_ALL
• AR_CASH_RECEIPT_HISTORY_ALL
• AR_RECEIVABLE_APPLICATIONS_ALL
• AR_ADJUSTMENTS_ALL

HZ_PARTIES
A party is an entity that can enter into a business relationship.
This table stores basic information about parties, which is true regardless of this relationship to the deploying company. Entities are modeled only once in HZ_PARTIES, regardless of how many roles they play. For example, if an organization is a customer, a distributor, and a partner, there is still only one record for them in HZ_PARTIES.

Parties can be one of four types:
Organization - Oracle Corporation
Person - Jane Doe
Group – Doc Household
Relationship - Jane Doe at Oracle Corporation

HZ_LOCATIONS
A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.
• This table stores information about an address such as: street address and postal code.
• This table provides physical location information about parties (organizations and people) and customer accounts.
• Records in HZ_LOCATIONS can store delivery and postal code information about a location, store latitude and longitude, and can be used to determine the appropriate calculations and tax rates for sales tax and VAT calculations.

HZ_PARTY_SITES
This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
• One party can point to one or more party sites.
• One location can point to one or more party site.
• Party sites serve as the intersection between parties and locations, allowing for a many-to-many relationship between the two.

HZ_RELATIONSHIPS
This table stores information about relationships between two entities, for example, one party and another party.
• The SUBJECT_ID and OBJECT_ID columns specify the relationship that exists between two parties. For example, if the party relationship type is “Parent Of,” then a holding company could be the “SUBJECT_ID” in the relationship while one of its subsidiaries could be the OBJECT_ID. Creating a party contact causes a party relationship to be created.
• A party can have different relationships with one or more other parties that can change over time.

HZ_ORGANIZATION_PROFILES
This table stores a variety of information about a party of type Organization.
• This table gets populated when a party of type ORGANIZATION is created. 
• Historical data is also stored in this table.

HZ_PERSON_PROFILES
This table stores a variety of information about a party of type Person.
• For example, this table could contain the correct spelling and phonetic pronunciation of the person’s name.
• Some information in this table may also be entered into the HZ_PARTIES table.

HZ_ORG_CONTACTS
This table stores a variety of information about an organization contact.
• The records in this table provide information about a contact position such as job title, rank, and department.
• This table is not used to store information about a specific person or organization. For example, this table may include a record for the position of Vice President of Manufacturing that indicates that the contact is a senior executive, but it would not include the name of the person in that position.

HZ_CUST_ACCOUNTS
This table stores information about customer/financial relationships established between a Party and the deploying company.
• Because a party can have multiple customer accounts, this table may contain several records for a single party. For example, an individual person may establish a personal account, a family account, and a professional account for a consulting practice.

HZ_CUST_ACCT_SITES_ALL
This table stores information about customer/financial account sites information.
Stores information about customer sites. One customer account can have multiple sites.

HZ_CUST_SITE_USES_ALL
This table stores information about the business purposes assigned to a customer account site.
• A customer account site can have multiple purposes, but each record in this table only specifies one purpose for a customer account site. For example, a customer account site may be assigned as a ship-to site in one record and as a bill-to site in another record.

HZ_CUST_ACCOUNT_ROLES
This table stores information about a role or function that a party performs as related to a customer account. For example, Jane Doe might be the Legal Contact for a specific customer account of Corporation ABC. Note that account ownership such as financial responsibility for an account is determined by the single party ID that is stored directly on the HZ_CUST_ACCOUNTS table.

HZ_CONTACT_POINTS
This table stores information about how to communicate with parties or party sites using electronic media or methods such as Electronic Data Interchange (EDI), e-mail, telephone, telex, and the Internet.
• Each medium or method should be stored as a separate method in this table. For example, the attributes of a complete telephone number connection should be stored in a record, while EDI information should be stored in a different record.

RA_CUSTOMER_TRX_ALL
This table stores invoice, debit memo, commit-ment, chargeback, bills receivable, and credit memo header information.
• Each row includes general invoice information such as customer, transaction type, and printing instructions.
• You need one row for each invoice, debit memo, commitment, and credit memo you create in Oracle Receivables and these are all distinguished by their transaction types stored in RA_CUST_ TRX_ TYPES_ALL.

RA_CUSTOMER_TRX_LINES_ALL
This table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines. It describes to the customer the charges that appear on these documents.

AR_PAYMENT_SCHEDULES_ALL
This table stores all transactions except adjust-ments and miscellaneous cash receipts. A miscellaneous cash receipt is one that is not connected to a customer.
• All customer-related activity is logged in this table.
• This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable,
receipt, or commitments.

RA_CUST_TRX_TYPES_ALL
This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.
• Each row includes Auto Accounting information
as well as standard defaults for the resulting invoices. The primary key for this table is CUST_TRX_TYPE_ID.

AR_TRANSACTION_HISTORY_ALL
This table is a Bills Receivable-specific table containing the history of a transaction’s lifecycle.
• A new row is created each time there is activity on the transaction or the status of the transaction has changed.
• This table stores the header for the Receivables posting information.

AR_DISTRIBUTIONS_ALL
This table stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments, credit memo applications, cash receipt applications, and bills receivable transactions.

AR_CASH_RECEIPTS_ALL
This table stores one record for each receipt entry.
• All cash receipts are logged in this table.
• Oracle Receivables creates records concurrently in the AR_CASH _RECEIPT_ HISTORY_ ALL, AR_PAYMENT_ SCHEDULES_ ALL, AR_DISTRI-BUTIONS_ALL, and AR_ RECEIVABLE_ APPLICA-TIONS_ALL tables for invoice-related receipts.
• For receipts that are not related to invoices, records are created in the AR_MISC_CASH_ DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ ALL table.

AR_CASH_RECEIPT_HISTORY_ALL
This table stores all of the activity that is contained for the life cycle of a receipt.
• Each row represents one step.
• The status field for that row tells you which step the receipt has reached.
• Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.

AR_RECEIVABLE_APPLICATIONS_ALL
This table stores all accounting entries for cash and credit memo applications.
• Each row includes the amount applied, status, and accounting flex field information.

AR_MISC_CASH_DISTRIBUTIONS_ALL
This table stores all accounting entries for miscellaneous cash applications.
• Miscellaneous cash cannot be invoiced, such as stock revenue, interest income, and investment income.
• AR_CASH_RECEIPTS_ALL stores one record for each payment, and this table stores one record for each distribution of the receipt.

AR_RECEIPT_CLASSES
This table stores the different receipt classes that you define.
• Receipt classes determine whether the receipt[s] belonging to this class are created manually or automatically, and whether the receipts go through the different steps in a receipt’s life-cycle.

AR_RECEIPT_METHODS
This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications.
• For automatically created receipts, a Payment Method defines the rules for creating these receipts.
• For manually created receipts, a Payment Method defines a user-definable type for the receipt.
• Each Payment Method is associated with a set of bank accounts, which forms the set of bank accounts you can assign to your receipt.

AR_ADJUSTMENTS_ALL
This table stores information about the adjustment applied to the Invoices.



Ur's
Amar Alam