Infolinks

Monday 14 May 2012

APPS A TO Z

Order to Cash (O2C) Cycle

Order to Cash (O2C) Cycle
Order to Cash means Customer’s Order Placing to Vendor’s Cash Receiving. When your final product is ready to be sold, you market it. The customer gets fascinated with the marketing campaign and decides to buy your product and from here starts the O2C cycle.
Step 1] Order Entry:
Customer sends details of order or sales dept brings order from customers. After that the order is entered in Order Management (OM)
Navigation: Order Management Super User> Orders Returns >Sales Orders
Here we need to enter the Customer Details (Customer Name , Number, Contact Ship to and Bill to address etc.), Order type. In the Lines tab we need to enter the Item to be ordered and the quantity required. Here we can also check the availability of the order. Here we can save the order. Once saved the Order Status is changed to ‘Entered’.
Key Tables:

    OE_ORDER_HEADERS_ALL – All header information is stored here.
    OE_ORDER_LINES_ALL – All the line information is stored here.

Step 2] Order Book :
When we book the order, we are just confirming and freezing our order.
The final step in the Sales Order Entry process is to Book the order. This signifies that the Order Entry process is complete and that the order is eligible for the next stage in the line flow for this order, as defined by its Transaction Type. Select the Book Order button. The Entry Status of the Order will change to Booked.
After Order Booking:
Order Header: Booked
Order Line : Awaiting Shipping
Shipping Transaction form: Ready to release
Table Level :

    OE_ORDER_HEADERS_ALL : Flow_Status_code –Booked
    OE_ORDER_LINES_ALL : Flow_Status_code – Awaiting Shipping
    WSH_DELIVERY_DETAILS : Released_Status – R ( means – Ready to release)

Step 3] Launch Pick Release :
Pick Release is the process in which the items on the sales order are taken out from inventory.
Navigation: Order Management Super User> Shipping > Release sales Orders > Release sales order
Based On rule: Select the Grouping rule the reaming details will default in Order, Shipping and Inventory tab
Order Tab:

    Order Number: Select the Order Number. Values for the Order Type and Customer fields of this form default to those for the order number you enter here.
    Ship Set: Select the Ship Set to be released. The Order Number must be selected first.

Shipping Tab:

    Auto creates Deliveries : Select Yes in this box to automatically create deliveries for  delivery lines once they are released
    Release Sequence Rule: Select Rule to specify the order in which the picking lines are released.
    Auto Pick Confirm –Yes/No

Inventory tab:

    Warehouse: Select the Warehouse
    Sub inventory: Select the Sub inventory
    Pick Slip Grouping Rule: To determine how released picking lines are grouped onto pick slips.
    Default Stage Sub inventory: Select the Default Stage Sub inventory

Click on Execute Now Button to complete the pick release of the order. Normally pick release SRS program runs in background . Once the program get completed these are the table get affected:

====(Pick Slip Report,Shipping Exception Report and Auto Pack Report)

    OE_ORDER_LINES_ALL (flow_status_code ‘PICKED’ )
    WSH_DELIVERY_DETAILS (released_status ‘S’ ‘submitted for release’ )
    mtl_txn_request_headers
    mtl_txn_request_lines
    Mtl_material_transactions_temp (link to above tables through move_order_header_id/line_id

Step 4] Pick Confirm the Order:
If Auto Pick Confirm in the above step is set to NO, then the following should be done.                            
Navigation: Inventory Super User > Move Order> Transact Move Order
In the HEADER tab, enter the BATCH NUMBER (from the above step) of the order. Click FIND. Click on VIEW/UPDATE Allocation, then Click TRANSACT button. Then Transact button will be deactivated then just close it and go to next step.

    Items are transferred from salable to staging Sub inventory.
    mtl_material_transactions
    mtl_transaction_accounts
    wsh_delivery_details (released_status ‘Y’ ‘Released’ )
    wsh_delivery_assignments

Step 5] Ship Confirm the Order:
The Shipping Transaction window provides a centralized workbench that consolidates three major shipping functions: planning, pick releasing, and ship confirming.
Navigation: Order Management Super User>Shipping >Transactions.
Here ship confirm interface program runs in background . Data are removed from wsh_new_deliveries.

    oe_order_lines_all (flow_status_code ‘shipped’)
    wsh_delivery_details (released_status ‘C’ ‘Shipped’)
    mtl_transaction_interface
    mtl_material_transactions(linked through Transaction source header id)
    mtl_transaction_accounts

Data are deleted from mtl_demand, mtl_reservations and Item is deducted from mtl_onhand_quantities.
Step 6] Enter Invoices in Receivables:
Run workflow background Process. Workflow Background Process inserts the records in RA_INTERFACE_LINES_ALL with

    INTERFACE_LINE_CONTEXT     =  ’ORDER ENTRY’
    INTERFACE_LINE_ATTRIBUTE1 =   Order_number
    INTERFACE_LINE_ATTRIBUTE3 =    Delivery_id

Then it spawns Auto invoice Master Program and Auto invoice import program which creates Invoice for that particular Order.
Navigation: Order Management >view >Requests
========(This will kick off concurrent programs like.INTERFACE TRIP Stop, Commercial Invoice, Packing Slip Report, Bill of Lading)
Underlying tables:

    RA_CUSTOMER_TRX_ALL will have the Invoice header information. The column INTERFACE_HEADER_ATTRIBUTE1 will have the Order Number.
    RA_CUSTOMER_TRX_LINES_ALL will have the Invoice lines information. The column INTERFACE_LINE_ATTRIBUTE1 will have the Order Number.



Step 7] COMPLETE LINE:
In this stage order line level table get updated with Flow status and open flag .

    oe_order_lines_all (flow_status_code ‘shipped’, open_flag “N”)

Step 8] CLOSE ORDER:
This is last step of Order Processing . In this stage only oe_order_lines_all table get updated .

    oe_order_lines_all (flow_status_code ‘closed’, open_flag “N”)
==========================================================================

PO: P2P Query based on the Purchase Order Number
PO: P2P Query based on the Purchase Order Number:-
--------------------------------------------------------------

You know the PO Order Number and you want to know all other details like Status of Invoice, Mode of payment etc from Purchasing to Payment. All those details can be found from the query given below.

This will work in all the 11i Instances.
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'
AND D.SEGMENT1 = 'Your Purchase Order Number'
==================================
API’s to Create User,Reset Password and Add Responsibility

API’s to Create User,Reset Password and Add Responsibility
I have created few queries using Oracle provided package:’FND_USER_PKG’. These queries might be very useful when you donot have the Oracle Apps front end access or you like to get in done through backend.
Using the below query, you can create a User in Oracle application.Just pass username, password and email id as parameters and it will create a user.

declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_password varchar2(30):='&Enter_Password';
v_session_id integer := userenv('sessionid');
v_email varchar2(30):=upper('&Enter_Email_Id');
begin
  fnd_user_pkg.createuser (
  x_user_name => v_user_name,
  x_owner => null,
  x_unencrypted_password => v_password,
  x_session_number => v_session_id,
  x_start_date => sysdate,
  x_end_date => null,
  x_email_address => v_email
  );
  commit;
  DBMS_OUTPUT.put_line ('User:'||v_user_name||'Created Successfully');
EXCEPTION
when others then
  DBMS_OUTPUT.put_line ('Unable to create User due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  ROLLBACK;
end;

May a times we forgot the apps password. Then you can use the below query to resent the password just in few seconds.


declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_new_password varchar2(30):='&Enter_New_Password';
v_status boolean;
begin
 v_status:= fnd_user_pkg.ChangePassword (
    username => v_user_name,
    newpassword => v_new_password
  );
  if v_status =true then
  dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
  commit;
  else
  DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  rollback;
  END if;
end;

Use the below query to add a responsibility to a user. The advantage here is that you donot require system administrator responsibility access to add a responsibility.



declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_resp varchar2(30):='&Enter_Responsibility';
v_resp_key varchar2(30);
v_app_short_name varchar2(50);
begin
  select
    r.responsibility_key ,
    a.application_short_name
  into v_resp_key,v_app_short_name
  from fnd_responsibility_vl r,
    fnd_application_vl a
  where
    r.application_id =a.application_id
    and upper(r.responsibility_name) = upper(v_resp);

  fnd_user_pkg.AddResp (
  username => v_user_name,
  resp_app => v_app_short_name,
  resp_key => v_resp_key,
  security_group => 'STANDARD',
  description => null,
  start_date => sysdate,
  end_date => null
  );
  commit;
  DBMS_OUTPUT.put_line ('Responsibility:'||v_resp||' '||'is added to the User:'||v_user_name);
EXCEPTION
when others then
  DBMS_OUTPUT.put_line ('Unable to add the responsibility due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  rollback;
end;
========================================
Duplicate Vendor List

Duplicate Vendor List
select pv1.vendor_name,
pv2.vendor_name DUP_VENDOR_NAME,
pv1.segment1 VENDOR_ID,
pv2.segment1 DUP_VENDOR_ID,
pvsa1.vendor_site_code SITE_CODE,
pvsa2.vendor_site_code SITE_CODE,
pvsa1.address_line1,
pvsa2.address_line1 DUP_ADDRESS_LINE1,
pvsa1.zip
from po_vendors pv1,
po_vendors pv2,
po_vendor_sites_all pvsa1,
po_vendor_sites_all pvsa2
where pvsa1.vendor_site_id <> pvsa2.vendor_site_id
and substr(replace(pvsa1.address_line1, ' '),1,20) =
substr(replace(pvsa2.address_line1, ' '),1,20)
and pvsa1.zip = pvsa2.zip
and pv1.vendor_id = pvsa1.vendor_id
and pv2.vendor_id = pvsa2.vendor_id
and pv1.vendor_id <> pv2.vendor_id
and pvsa1.address_line1 <> 'YOUR ADDEDSS’
order by 1;
=====================
Sales Order Interface
Standard import Program: ORDER IMPORT
Interface tables:
OE_HEADERS_IFACE_ALL
OE_LINES_IFACE_ALL
OE_ACTIONS_IFACE_ALL
OE_CUSTOMER_INFO_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL
OE_PRICE_ATTS_IFACE_ALL

Base tables:
OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
QP_PRICING_ATTRIBUTES

If importing customers together with the order,
OE_ORDER_COST_IFACE_ALL
Base tables:
HZ_PARTIES
HZ_LOCATIONS


Validations:
--Check for sold_to_org_id. If does not exist, create new customer by calling create_new_cust_info API.
--Check for sales_rep_id. Should exist for a booked order
--Ordered_date should be exist.------------header level
--Delivery_lead_time should exist. --------line level
--Earliest_acceptable_date should exist
--Freight_terms should exist.

PO Interface (1)
Requisition Interface
Standard import program: Requisition import

Interface tables:PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_INTERFACE_ERRORS

Base tables:PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTION_ALL

Validations:--Check for interface transaction source code, requisition destination type
--Check for quantity ordered, authorization status type.
INV Interface (2)
1. Item Interface

Standard import program: Item Import
Interface tables:MTL_SYSTEM_ITEMS_INTERFACE
MTL_TRANSACTIONS_INTERFACE
MTL_ITEM_REVISION_INTERFACE
MTL_DEMAND_INTERFACE
MTL_ITEM_CATEGORIES_INTERFACE
MTL_CROSS_REFERENCES_INTERFACE
MTL_INTERFACE_ERRORS

Base tables:MTL_SYSTEM_ITEMS_B
MTL_ITEM_REVISIONS_B
MTL_ITEM_CATEGORIES
MTL_CROSS_REFERENCES
MTL_ITEM_STATUS
MTL_ITEM_TEMPLATES
MTL_CATEGORY_SETS_BMTL_CATEGORIES_B

Validations:-- check for valid item type
-- check for valid part_id/segment1 of the source table
-- Validate part_id/segment1 for master org
-- Validate and translate template id of the source table
-- Check for valid template id (attributes are already set for items default attributes for that template, i.e., purchasable, stockable, etc)
-- check for valid item status
-- Validate primary uom of the source table
-- Validate attribute values
-- Validate other UOMs of the source table.
-- Check for unique item type. Discard the item, if par t has non-unique item type.
-- check for description, inv_um_uniqueness
-- Validate organization id.

ERROR CHECKING:--When importing multiple revisions, if one record for an item fails validation, all revisions for that item fail. Resolve failed rows by checking the MTL_INTERFACE_ERRORS table.
Select table_name, column_name, error_message, message_name
From mtl_interface_errors;

2. On hand quantity Interface
Standard import program:
Interface tables:MTL_TRANSACTIONS_INTERFACE
MTL_TRANSACTIONS_LOTS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE

Base tables:MTL_ONHAND_QUANTITIES
MTL_LOT_NUMBERS
MTL_SERIAL_NUMBERS

Validations:--Validate organization_id, organization_code
-- Validate inventory_item_id
-- Transaction period must be open.

Labels: Interface

AP Interface (2)
1. Invoice Interface

Standard import program: Payables Open Interface Import

Interface tables:
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE

Base tables:
AP_INVOICES_ALL (header information)
AP_INVOICE_DISTRIBUTIONS_ALL (line information)


Validations:--check for valid vendor
--check for valid vendor site code
--check if record already exists in Payables interface table.


2. Vendor/Supplier Interface

Standard import program:Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import

Interface tables:AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT

Base tables:PO_VENDORS
PO_VENDOR_SITES_ALL
PO_VENDOR_CONTACTS

Validations:--check if a vendor already exists with the same name as the TIMSS customer mail name
-- Check if the proper site code and id exists based on the site code from TIMSS
-- Check for uppercase value of the vendor name exists in oracle and in TIMSS, vendor name is mixed case, a new Oracle vendor will not be created.

AR Interfaces (3)
1. Customer Interface

Stardard import concurrent program: Customer Interface

Interface table:
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOEMR_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL

Base table: skipped
Validations: --check if legacy values fetched are valid
-- Check if customer address site is already created
-- Check if customer address site use is already created
-- Check if customer header is already created
-- Check if the ship_to_site has associated with bill_to_site
-- Check if associated bill_to_site is created or not.
--Validate profile amounts: validate cust_account_id, customer_status
--Check if the location already exists in HZ_LOCATIONS. If not, create new location.

2. Auto Invoice Interface

Stardard import concurrent program:
Auto Invoice Master Program
Auto Invoice Import Program
Auto Invoice Purge Program

Interface tables:RA_INTERFACE_LINES_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL

Base tables:RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL
RA_CUSTOMER_TRX_LINE_SALESREPS
RA_CUSTOMER_TRX_TYPES_ALL
RA_CUST_TRX_GL_DIST_ALL (connect to GL)
RA_BATCHES
AR_PAYMENT_SCHEDULES_ALL
AR_RECEIVABLES_APPLICATIONS
AR_CASH_RECEIPTSAR_ADJUSTMENTS

3. Auto Lockbox Interface

Standard Import Program: (N): Lockbox -> Submit Lockbox Processing

Interface tables: AR_PAYMENTS_INTERFACE_ALL
AR_INTERIM_CASH_RECEIPTS_ALL
AR_INTERIM_CASH_RCPT_LINES_ALL

Base tables:AR_CASH_RECEIPTS_ALL
AR_RECEIVABLES_APPLICAITONS_ALL
AR_PAYMENT_SCHEDULES_ALL
AR_CASH_RECEIPT_HISTORY_ALL

GL Interfaces (2)
1. Journal Interface (GL Interface):
Stardard import concurrent program: Jounral Import

Interface table:GL_interface
Base table:
GL_JE_BATCHES
GL_JE_HEADERSGL_JE_LINES

Validations:--check SOB, journal source name, journal category name, actual flag
A-actual amounts, B-budget amount, E-encumbrance amount
if you enter E in the interface table, then enter appreciate encumbrance ID.
if you enter B in the interface table, then enter appreciate budget ID.
--Check if accounting data or GL data based period name is valid (i.e., not closed)
--Check if accounting data falls in open or future open period status.
--Check chart of account id based on SOB id
--Check if valid code combination
--Check if ccid is enabled
--Check if record already exists in GL interface table
--Check if already journal exists in GL application.
2. Budget Interface :
Stardard import concurrent program: Budget Upload

Interface table:
GL_BUDGET_INTERFACE

Base table:GL_BUDGETS
GL_BUDGET_ASSIGNMENTS
GL_BUDGET_TYPES
Columns:
Budget_name, budget_entity_name, currency_code, fiscal_year, budget_entity_id, set_of_books_id, code_combination_id, period_type, dr_flag, period(n)_amount, segment(n), and who columns

Account Receivables data model
For preliminary understanding, we will break the Module functionality into following components:1) AR setups
2) Transactions Workbench
3) Receipts workbench
4) Interfaces

We will take each component one by One:

1) AR setups :
System Options : AR_SYSTEM_PARAMETERS
Transaction Types : RA_CUST_TRX_TYPES_ALL
Transactions Sources : RA_BATCH_SOURCES_ALL
Banks : AP_BANK_BRANCHES
Bank accounts : AP_BANK_ACCOUNTS_ALL
(Customer/Internal)
Receipt Classes : AR_RECEIPT_CLASSES
Receipt Sources : AR_BATCH_SOURCES_ALL
Payment Methods : AR_RECEIPT_METHODS
Customers : HZ_CUST_ACCOUNTS
HZ_CUST_SITE_USES_ALL
HZ_CUST_PROFILE_AMTS
HZ_CUST_ACCOUNT_ROLES
HZ_PARTIES
HZ_PARTY_SITES
HZ_PARTY_SITE_USES

Customer profile class: HZ_CUST_PROFILE_CLASSES
HZ_CUST_PROF_CLASS_AMTS

Dunning letters : AR_DUNNING_LETTERS_B
AR_DUNNING_LETTERS_TL
Dunning Letter Sets : AR_DUNNING_LETTER_SETS
AR_DUNNING_LETTER_SET_LINES

Statement Cycles : AR_STATEMENT_CYCLES
AR_STATEMENT_CYCLE_DATES_ALL

Payment Terms : RA_TERMS
RA_TERMS_LINES
RA_TERMS_LINES_DISCOUNTS
2) Transactions Workbench:
Batch : RA_BATCHES_ALL

Headers : RA_CUSTOMER_TRX_ALL

Lines : RA_CUSTOMER_TRX_LINES_ALL

Sales credits : RA_CUST_TRX_LINE_SALESREPS_ALL

Distributions : RA_CUST_TRX_LINE_GL_DIST_ALL

Payment schedules : AR_PAYMENT_SCHEDULES_ALL

3) Receipts Workbench :

Batch : AR_BATCHES_ALL

Receipts : AR_CASH_RECEIPTS_ALL
AR_CASH_RECEIPT_HISTORY_ALL

Receipt Applications : AR_RECEIVABLE_APPLICATIONS_ALL

4) Interfaces :

Auto invoice : RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL


Receipts/Lockbox : AR_PAYMENTS_INTERFACE_ALL
AR_INTERIM_CASH_RECEIPTS_ALL
AR_INTERIM_CASH_RCPT_LINES_ALL

Customers : RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CONTACT_PHONES_INT_ALL

Tax Locations and Rates: AR_TAX_INTERFACE

Account payables data model
For preliminary understanding, we will break the Module functionality into following components:1) AP setups
2) Invoice Workbench
3) Payments workbench
4) Invoice and Payments accounting
5) Interfaces


We will take each component one by One:
1) AP setupsVendors
Vendors : PO_VENDORS
Vendors Sites : PO_VENDOR_SITES_ALL
Banks
Banks and bank branches : AP_BANK_BRANCHES
Bank accounts : AP_BANK_ACCOUNTS_ALL
Payable Documents : AP_CHECK_STOCKS_ALLPay termsPayment Terms : AP_TERMS
Pay Term Lines : AP_TERM_LINES

Payables options
Payables options : AP_SYSTEM_PARAMETERS

Distribution sets

Distribution sets : AP_DISTRIBUTION_SETS_ALL

Distribution set lines : AP_DISTRIBUTION_SET_LINES_ALL

2) Invoice Workbench :

Invoice Batch : AP_BATCHES_ALL

Invoice Headers : AP_INVOICES_ALL

Invoice Distributions : AP_INVOICE_DISTRIBUTIONS_ALL

Invoice Scheduled Payments : AP_PAYMENT_SCHEDULES_ALL

Invoice Holds : AP_HOLDS_ALL3) Payments Workbench :
Payment Batch Sets : AP_PBATCH_SETS_ALL
AP_PBATCH_SET_LINES_ALL

Payments : AP_CHECKS_ALL
AP_INVOICE_PAYMENTS_ALL

Payment Distributions : AP_PAYMENT_DISTRIBUTIONS_ALL

4) Invoice and payments accounting :

Accounting Events : AP_ACCOUNTING_EVENTS_ALL

Accounting Headers : AP_AE_HEADERS_ALL

Accounting Lines : AP_AE_LINES_ALL

5) Interfaces

Invoice interface : AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE


Supplier/Vendor interface : AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT

General ledger data model
For Preliminary Understanding of the GL data model, we will split it into following components:

1) GL setups
2) Journals
3) Budgets
4) Interfaces

We will take each component one by One:1) GL setups :

Period types : GL_PERIOD_TYPES

Accounting Calendar : GL_PERIOD_SETS, GL_PERIODS

Accounting ID : GL_CODE_COMBINATIONS

Chart of Accounts : FND_FLEX_VALUE_SETS
(Accounting Flex STR) FND_ID_FLEX_STRUCTURES
FND_ID_FLEX_SEGMENTS
FND_FLEX_VALUES

Set of Books : GL_SETS_OF_BOOKS

Period Statuses : GL_PERIOD_STATUSES

Currencies : FND_CURRENCIES

Currency Conversion Rates : GL_DAILY_RATES

Period Rates : GL_TRANSLATION_RATES

2) Journals (Actual / Budget/ Encumbrance) :

Journal Batch : GL_JE_BATCHES

Journal Header : GL_JE_HEADERS

Journal Lines : GL_JE_LINES

Journal Posting Interim Table : GL_POSTING_INTERIM

Balances : GL_BALANCES
3) Budgets :

Budget Definition : GL_BUDGETS
GL_BUDGET_VERSIONS
Budget Organizations : GL_BUDGET_ENTITIES

Budget Org Ranges : GL_BUDGET_ASSIGNMENT_RANGES

Budget Org Range Assignments: GL_BUDGET_ASSIGNMENTS

Budget Journals : GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES4) Interfaces:

Journals Interface : GL_INTERFACE
(To Upload Journals from
Feeder and Legacy Systems)

Budgets Interface : GL_BUDGET_INTERFACE
(To upload Budget amounts)

Daily Rates Interface : GL_DAILY_RATES_INTERFACE
(To Upload Conversion rates)
Posted by Prasanth Reddy at 5/11/2011 04:29:00 AM 0 comments
Email ThisBlogThis!Share to TwitterShare to Facebook
Labels: Interfaces
Reactions:     
Tuesday, 19 April 2011
Interfaces

Interfaces:
   -------------


Interface is one of the Program which will be used to transfer the data from Oracle
database tables in to flat file   (or)
Flat files      into  Database tables

We have two types of Interfaces.

1)Inbound Interface
2)outbound Interface

Outbound Interface will be used to extract the data from oracle Database tables into
the flat files.

Inbound Interface will be used to upload the data from legacy system (Flat files) into
Oracle Applications base tables.

While developing the outbound Interface we will use UTL_File to Extract the data.
While Developing the Inbound interface  we will use SQL * loader to import the data
into base taqbles.

Converstion Vs Interfaces

Interfaces:
It is schedule concurrent Process which will be executed multiple times
We will not be knowing flat file Volume
We need to handle all the expected exceptions
Error Reports and sending mail alerts, If any error occurs
Only in Enhancement or customization projects

Conversion:
One time data transfer
We will be know exact falt file Volume
No need to know the exceptions
Not required to upload all records
Only in Implementation,Migration or up-gradation projects

UTL_FILE Package :
==================

this is One of the PL/SQL Package which will be used to transfer the data
from table to files
from files to tables

But when we are working for file to table we will use SQl *Loader
to transfer from table to file we have no alternative we have to use UTL_FILE.

We will use following three functions to generate the file.

1)Utl_File.fopen     = To open (or) Create the file
2)Utl_File.Put_line  = To Transfer the data into the File.
3)Utl_File.fclose    = To close the File after Data transfer.



outbound Interface Process:
=============================

1)Develop the PL/SQL Program (Either Procedure or Package)
2)Write the Cursor to retrieve the data from database tables.
3)Create file or Open the File by using UTL_File.fopen().
4)Open the Cursor
5)If any validations are there write the validations
6)Transfer the Data into File by using UTL_File.Put_Line().
7)Close the Cursor.
8)Close the File by using UTL_File.fclose();
9)Register the Procedure or Package as Concurrent Program and submit from SRS Window.



For Ex : We required the flat file with following Format.

Supplierno,Supliername,Cdate,Sitename


Develop the Program to Transfer Supplier Purchase orders datainto file


Supplier No from
Supplier No To 

PoNumber,POtype,Amount,Cdate,No,Name,Site


Validations:

1)If Parameters are null select all the supplier details
2)If 'from' is given  'To' is not given take till Last Supplier
3)If 'To' is given and 'from' is not given take from first.
4)If Purchase order amount is more then 3500 then only transfer into file.
========================

AR COMPLETE MANUAL

AR COMPLETE MANUAL
Introduction
The procedures for performing period-end processing in Oracle Receivables Release 11i.
Business Requirements
Oracle Receivables requires periodic internal reconciliation of the transactions entered into the Accounts Receivables system. We want to verify the impact of daily activity on the Aged Trial Balance Report and to confirm amounts posted to the General Ledger.
Oracle Receivables provides a comprehensive set of reports to facilitate reconciliation of outstanding customer balances, transactions, receipts, and accounts balances.
Oracle Receivables provides the functionality to enable reconciliation of your sub-ledger before posting to the general ledger. Posting to the General Ledger allows extraction of details from Oracle Receivables, and creation of journal entries in the General Ledger. After posting to the General Ledger, it is possible to reconcile Oracle Receivables with the general ledger by verifying that all the correct journal entries were made
Steps -- Procedures
The following steps are taken in performing period-end processing for Oracle Receivables:
Complete All Transactions for the Period Being Closed
Ensure that all transactions have been entered for the period being closed.
Completing all transactions for Oracle Receivables:
1.    Complete Invoicing, Credits and Adjustments
2.    Complete Receipts and Reversals
3.    Complete Invoice and Customer Import
4.    Complete LockBox Processing
If you import transactions from an external system or Oracle Projects, ensure you have imported all transactions and master files, and reviewed all audit trails for completeness.
Reconcile Transaction Activity for the Period
Reconcile the transaction activity in Oracle Receivables before posting to the General Ledger. This reconciliation process checks that Oracle Receivables transactions balance with themselves, ensuring that all postable items are reflected on the Sales Journal. Run the following reports for the same accounting period date range:
The Transaction Register
This report details all the transactions (i.e. invoices, debit memos, credit memos, deposits, guarantees, and chargebacks) entered with a GL date between the period start and end dates specified for the period being reconciled. This report shows transactions entered and completed.
The Sales Journal By Customer Report and the Sales Journal By GL Account Report
This report enables review of all transactions for the specified period. The summary totals for the sales journal are by Posting Status, Company, and Transaction Currency. This report details, by account type (i.e. receivables, revenue, freight, tax), the general ledger distributions for posted and/or unposted invoices for the specified period.
The total on the Sales Journal by GL Account should equal the total of postable items listed on the Transaction Register. If any discrepancies are evident, research the customer balances to find out which balance does not tally, using the Sales Journal by Customer report.
By using the following formula, ensure that the Transaction Register matches the Sales Journal:
Transaction Register (Postable Items) + 2 * Credit Memo Total = Sales Journal (Debits plus Credits)
e.g. $100 + (2 * $20) = Debits $120 + Credits $20
($120 Debits - $20 Credits)
Attention: The Transaction Register total for any credits must be adjusted as they are negative on the Transaction Register and positive on the Sales Journal.
Attention: Ensure that the monthly transaction total is accurate and that no distribution issues exist.
Reconcile Outstanding Customer Balances
Reconcile the outstanding customer balances at the beginning of a specified period with the ending balance for the same period, using the following formula, known as the RollForward Formula:
Period-End Balance = Outstanding Balance at Start of Period + Transactions + Adjustments - Invoice Exceptions - Applied Receipts - Unapplied Receipts
The following table represents the various components that affect a customer’s balance and the reports which can be run and reviewed to reconcile these components:
Reconcile Outstanding Customer Balances:
1.    Beginning Balance - Aged Trial Balance (As of the first day of the accounting period)
2.    Transactions - Transaction Register
3.    Adjustments - Adjustment Register
4.    Invoice Exceptions - Invoice Exceptions Report
5.    Applied Receipts -Applied Receipts Register (Identify payments received from customers)
6.    Unapplied Receipts - Unapplied Receipts Register (Identify payments received from customers)
7.    Ending Balance - Aged Trial Balance (As of the last day of the accounting period)
Attention: You can use the Invoice Exceptions Report to adjust the Transaction Register for any transactions which are not open to Receivables and therefore do not show up in the agings.
Review the Unapplied Receipts Register
Use the Unapplied Receipts Register to review detailed information about your customers on-account and unapplied payments for the date range that you specify. You can use this report to determine how much your customer owes after taking into account all on-account and unapplied amounts. Receivables displays information about your on-account or unapplied payment such as GL date, batch source, batch name, payment method, payment number, payment date, on-account amount, and unapplied amount. This report includes both cash and miscellaneous receipts.
If any of the Receipts listed can now be applied to outstanding transactions, then perform this action by re-querying the receipts and following the normal application procedure.
Reconcile Receipts
Ensure that Oracle Receivables receipts balance with themselves by running the following reports:
Receipts Journal Report
This report displays details of receipts that appear in the Journal Entry Report. The Journal Entry Report shows the receipt numbers that contribute to a particular GL account. Using the receipt number, you can review the detailed information on the Receipts Journal Report.
Receipt Register
Use this report to review a list of receipts for a specified date range.
The total of the Receipts Journal Report should equal the total of all the receipts in the Receipt Register for the same GL date range. Both reports display invoice related receipts and miscellaneous receipts.
Reconcile Receipts to Bank Statement Activity for the Period
(Refer to Chapter 8 Period-End Procedures for Oracle Cash Management)
Attention: The General Ledger transfer process must be re-run for any miscellaneous accounting entries generated from the bank reconciliation, for transfer to the General Ledger.
Post to the General Ledger
Prior to posting to the general ledger from Oracle Receivables, the Receipts Journal Report and Sales Journal display the transactions that would be posted to the General Ledger (providing the posting process was run for the same GL date range). After internally reconciling the transactions and receipts using these two reports, it is possible to perform external reconciliation during and after the posting process.
The posting process for Oracle Receivables involves two steps:
General Ledger Transfer
The general ledger transfer process transfers transaction and receipt data to Oracle General Ledger’s interface table.
Journal Import
This process follows the General Ledger Transfer. This process can be initiated from Oracle Receivables, or from Oracle General Ledger. The process creates unposted journals in Oracle General Ledger. A separate posting process (The GL Post) is then run in Oracle General Ledger, to create posted journal entries. Oracle Receivables provides reporting tools to enable tracking and reconciliation of the entire posting process.
Reconcile the General Ledger Transfer Process
Compare the GL Transfer process execution report, that details the total debits and credits transferred, with the sales and receipt journals totals and check that they match. Ensure that the date ranges for the reports and the GL Transfer are identical.
Oracle Receivables will generate the Unposted Items Report if there are any transactions or receipts that could not be transferred to the general ledger interface table. If this is the case, the total un-transferred items must be considered when reconciling the sales and receipts journals with the general ledger transfer execution report.
Once transactions and receipts have been transferred to the GL interface table, Oracle Receivables regards these items as having been ‘posted’ within the sub-ledger. Account balances for transactions and receipts can be reconciled by generating the Sales Journal by GL Account Report, the Receipts Journal Report (in ‘transaction’ mode) and the Journal Entries Report for posted items. The account totals in the Sales and Receipt journals should match the corresponding account balances in the Journal Entries Report.
Attention: The ‘Detail by Account’ version of the Journal Entries Report may be the most useful for reconciliation in this case.
Reconcile the Journal Import Process
Journal Import produces an execution report detailing the total debits and credits for the journals created by the import process. These totals match the totals on the GL transfer execution report.
Run the Publish Journals-General Report with a Posting Status of Unposted from Oracle General Ledger to view the journals created. The grand totals on this report match the Journal Import Execution Report.
Print Invoices
Once you are satisfied that customer balances are reconciled, ensure all the invoices generated during the month have been printed and issued.
If consolidated invoice functionality is used, then ensure that the consolidated invoices have been generated for the current period.
Print Statements (Optional)
Once you are satisfied that the customer balances are reconciled and the business procedure is to generate and issue Statements. Initiate the printing of all monthly (periodic) cycle statements.
Print Dunning (Reminder) Letters (Optional)
Once you are satisfied that the customer balances are reconciled, and the business procedure is to generate and issue Dunning or Reminder Letters. Initiate the printing of all dunning letters/sets.
Close the Current Oracle Receivables Period
Close the current period in Oracle Receivables using the Open/Close Accounting Periods window.
Reconcile Posted Journal Entries
After running the GL posting process in Oracle General Ledger, run the Publish Journals-General Report with a Posting Status of Posted from the Oracle General Ledger, and verify that the grand totals from this report match the Journal Import Execution Report.
Review the Unposted Items Report
Receivables prints the Unposted Items Report for all items that are not posted for the specified GL date range. There are two ways to generate this report: through the Submit Requests window or by running the General Ledger Interface Program. If you submit this report through the Submit Requests window, the output will consist of all unposted items for the specified GL date range.
The General Ledger Interface Program automatically generates this report if there are items that you attempt to transfer to your general ledger that are out of balance. In this case, Receivables prints a reminder on the Posting Execution Report to check your log file for out of balance items.
Using the Submit Requests window to generate this report, submit with a GL date range for at least the current financial year. This report should not generate any output if all Receivables transactions have been successfully posted to General Ledger.
If there are any unposted items for the current or prior periods, then re-open both appropriate Receivables and General Ledger Periods and initiate another posting.
Open the Next Oracle Receivables Period
Open the next period in the Oracle Receivables using the Open/Close Accounting Periods window.
Run Reports for Tax Reporting Purposes (Optional)
A variety of standard reports can be used to provide tax information which is required to be reported to the relevant Tax Authority, including withholding tax.
The Financial Tax Register can be used to view the output from the Tax Reporting Ledger using Reports Exchange and Application Desktop Integrator (ADI). Using these products you can change the layout of the report, publish the report in different formats, and export the data to a tab delimited or HTML file.
The Tax Reporting Ledger consists of accounting information created in Oracle Receivables, Oracle Payables, and Oracle General Ledger. The Financial Tax Register uses this data to generate Tax Register reports using the Rxi reporting tool.
The following tax registers are available:
§  Tax Register
§  Interim Tax Register
§  Nonrecoverable Tax Register
The following summary levels are available within each Tax Register:
§  Transaction Header
§  Transaction Line
§  Accounting Line

Run Archive and Purge Programs (Optional)
The Archive and Purge cycle is divided into four separate processes, Selection and Validation, Archive, Purge, and optionally Copying to a file. The Selection and Validation and Archive processes form the Archive-Preview program. This program selects eligible transaction using criteria you specified, validates the data to identify the transaction chains, then stores this information in the archive tables. The Purge program uses the information in the archive tables to delete eligible transactions from the database tables. Alternatively, you can run selection and validation, archive, and purge processes together using the Archive and Purge program. The final process is to transfer the archive data to a separate storage medium.
Warning: You should not use the Receivables Archive and Purge program if you are using cash basis accounting.
=========================
Duplicate rows in the table


Duplicate rows in the table:-
--------------------------------

The following query can be used to get the duplicate records from table.

SELECT * FROM 'Your table name' WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM 'Your Table Name' GROUP BY'Your duplicate values field name');

Example:-

SELECT * FROM emp WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp GROUP BY ename);

To eliminate/delete the duplicate rows from the table, you can use the following query.

DELETE 'Your table name' WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM 'Your Table Name' GROUP BY 'Your duplicate values field name');

Example:-

DELETE emp WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp GROUP BY
ename);
=====================
PO_AP LINK QUERIES (2-WAY, 3-WAY,4-WAY)

                                                                    
                                            
AP-PO Link Queries

-- VENDOR, PO AND INVOICE DETAILS (2 WAY MATCH)

SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
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 APPROVED?",
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
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
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 g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'N'
AND g1.receipt_required_flag = 'N'

-- VENDOR, PO AND INVOICE DETAILS (3 WAY MATCH)

SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
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 APPROVED?",
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
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
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 g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'N'
AND g1.receipt_required_flag = 'Y'

-- VENDOR, PO AND INVOICE DETAILS (4 WAY MATCH)

SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
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 APPROVED?",
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
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
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 g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'Y'
AND g1.receipt_required_flag = 'Y'

-- VENDOR, PO, INVOICE AND PAYMENT DETAILS

SELECT DISTINCT a.org_id "ORG ID", e.segment1 "VENDOR NUMBER",
e.vendor_name "VENDOR NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE",
d.segment1 "PO NUM", d.type_lookup_code "PO TYPE",
c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED",
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 APPROVED?",
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.check_id, i.check_number,
h.invoice_payment_id,
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,
ap.ap_suppliers e,
ap.ap_supplier_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 c.po_header_id IS NOT NULL
AND a.payment_status_flag = 'Y'
AND d.type_lookup_code != 'BLANKET'
ORDER BY E.VENDOR_NAME
========================================
Procedure Registration Steps In APPS

Procedure Registration Steps:


1)Develop the procedure and compile  at SQL prompt
2)Connect application select system administrator Responsbility create
  Executable by selecting the Execution Method as 'PL/SQL Stored Procedure'
3)Create Concurrent Program and attach Executable to the Program and add the
  Parameters and  incompatible programs.
4)Create Request group attach concurrent Progrtam
5)Attach Request group to the reponsibility  
6)Responsiboility  will be added to the user.
 User will submit the request from SRS Window.

Syntax:
Create Or Replace Procedure <ProcedureName> (Errbuf OUT varchar2,
          Retcode OUT varchar2,
          P1    IN NUMBER,
          P2    IN VARCHAR2,
          P3    IN DATE) AS
Local variable,Cursor,Collections Declare;
Begin

If statement
For Loop
Procedure Calling           
DBMS_OUTPUT.Put_Line - This will not be used instead of this Fnd_File API will be used

FND_FILE.PUT_LINE(Fnd_File.Log   ,'Message'||Variable Name);
FND_FILE.PUT_LINE(Fnd_File.Output,'Message'||Variable Name);
Exception
When Other then
-Exception Statements;
End <Procedure Name>;

PL/SQL Procedure with Parameter:
================================
If we have any user defined Parameters then we have to register these parameters at the
time of Creating the COncurrent Program by selecting the Parameter button
enter the Seqno
   Parameter Name
   Value Set
Note : Token Field will be disabled.
Here First Parameter value will be passed to the first variable
Second Parameter will be passed to the second variable and so on........

When we are registering the report as C.P then only we required TOKEN field.
Because report builder bind variables may or may not be in the sequence that's why
we have to map with Token field.

Where as in Procedure variables position is fixed then TOKEN field will be disabled.
======================

PURCHASING END TO END SETUP STEPS:

PURCHASING END TO END SETUP STEPS:

Attach the Profile HR:user Type = HR with Payroll User
1)Locations in HRMS
  (US HRMS Manager =.Work Structure=>Location)
2)Business groups in HRMS
  Assign the Profiles
SOB Creation
Create Inventory Work Calendar attach to Inventory organization
Inventory=>Setup=>Organizations=>Calendar=>Create Calendar=>Tool Menu=>build

3)Create all Organization(SOB,LE,OU,IO)
4)Organization hierarchy
5)jobs
6)Positions
7)Position hierarchy
8)Create Two Employee 1)Clerk 2)Manager Assign the manager to the Clerk.
9)Submit Program from SRS Window Fill Employee Hierarchy.
9)Creation of Combo Responsibility(PO,INV,GL,AP) attach Menus, attach RequestGroup
10)Attach the Profiles
    Businesgroup
    Set of Books
    Operating Units
Attach the Profile called (MO:Operating Unit for the Responsibility)
11)Run the Porgram called Replicate Seed Data.
12)Goto Purchasing Assign the Set of Books. 
PO=>Setup=>Organization=>Set of Books=>Choose



13)Purchasing Options
14)Recceving Options
15)Financial options
16)Find the Documents and assign the Approval hierarchy (IBM hierarchy)
17)Assign the Approve Hierarchy to the All Documents.
  Purchasing =>Setup=>Purchasing=>Document Types.
18)Define the Approval Group (Setup=>Approval=>approval Groups)
   Assign the Hierarchy to the Document Types.
   Purchasing=>Document Types.
19)Assign Approval groups to the Documents.
20)Item Creation in Inventory (Inv=>item=>master Items)
21)Creation SubInventory      (Inv=>Setup=>Organizations=>Subinventories)
22)Create Users from sytem administrator to the Employees.
  Include the Employee names in the Buyers List.
23)Login with Clerk and create Requisition if limit crossed then goto manager login
   open the notification summary and Approve.
======================
PURCHASE ORDER USE FULL INFORMATION

                   PURCHASE ORDER USE FULL INFORMATION                                                 
                                                                    
                                            
PO Application will be used to capture the purchasing information.
Oracle is developed pre defined forms and as well as Concurrent Programs and other
related programs.Client Directley can use those forms and Programs or client
can customize the existing objects(Forms,Reports,Programs)

During the PO application flow we can find the three types of people
1)Requestor : Employee who require the materials
2)Preparer  : Employee who is going to prepare the Document
3)Buyer     : Employee who is having the authority to purchase the materials from the
              Suppliers.

Requisition: is one of the purchasing document will be prepared by the employee when
   ever he required the materials or Services or Training and so on.
we have two types of Requisitions 1)Internal
     2)Purchase

Internal requisition will be created if materials are receiving from another Inventory
inside of the organization.
Purchase requisition will be created while purchasing the materials from the Suppliers.

Requisitions=>Requisitions

We will enter the Requisition at three level 1)Header
          2)Line
          3)Distributions.

Open the Requisition form enter the Reqno and select the type at Header level
 Enter the Items information at line level like Item name,qty,unitprice,tax and so on
select Distributions button enter the Distributions details.
Save
Select the Button called Approve button to go for approving the Requisition Document
Open the Requisition summary form.
Enter the Reqno select find button we can find the Requisition status wether it is
approved or not.
select Tools menu => View Action History to find the history details
Select Tools Menu  =>Control option to Cancel the requisition.

RFQ Document:(Request For Quotation)
=============

Once the Requisition is Approved Buyer will prepare thre RFQ document which will be
delivered to the supplier. Supplier will respond for that with quotation.
we have Three types of RFQ documents

BID RFQ:This will be prepared for the secific fixed quantity and there won't be any
        PriceBraeaks(Discounts).

catalog RFQ: This will be create for te materials which we will purchase from the
        suppliers regularley , and large number of quantity. Here we can specify the
        Price Breaks.

Standard RFQ:  This will be prepared for the Items which we will purchase only once
        not very often,Here we can include the Discounts information at different
        auantity levels.
RFQ Information will be entered at 3 Level
  1)Headers
  2)Lines
  3)Price Breaks(CATALOG,STANDARD) or Shippments (Only for Bid RFQ)

Terms And Conditions:
While creation of the RFQ documents we will select the Terms button and we will enter
the terms abd condition details.

Payment Terms: When Organization is going to make the payment and Interest rates
Fright  Terms: Who is going to Bear the Tansportation chargers wether Buyer or Supplier
FOB(FreeOnBoard): If any materials damage or any missing quantity is there then the
             the responsiboility of those materials.
Carrier     : In which Transportation Company Organization Required Materials
              Transportation company Name.

Open the RFQ Form

RFQ and Quotations=>RFQ's
select TYpe and Dates and so on
enter the Items details at line level
select terms button enter the Terms and Condition Details
Select the Price Braks button enter the Price break details
Save
Select the suppliers button enter the suplier details (Who are receiving this Document)
Select the Button called Add from List to Include the supplier list automatically.

 Buyer Name     : TABLE (Internally buyer ID should pass)  - Optional
    


RFQNo               Shipto
Type         Billto
Due   date        Curr
Close date        Total
creation date       User(created_by)

Lineno    Item     UOM  Price   Shipno   Qty   Price  Discount  
-----    





















Quotations:
===========
Quotation is another purchasing document we will receive from the Supplier which
contains the supplier quote details , Price, Payment terms and so on.

Whatever the quotations we have received from the supplier we will enter in the system
through form.

We have three types of Quotations 1)Bid 2)Catalog 3)Standard

For Bid RFQ      we will  receive Bid      quotation from the Supplier
For Catalog RFQ  we will  receive Catalog  quotation from the Supplier
For Standard RFQ we will  receive Standard quotation from the Supplier.

After enter all the quotations in the system management will do quote analysis as per
that one best quotation will be elected as Purchase Order.

    Quotation Report
   
    Item Name   (Table Value set MTL_SYSTEM_ITEMS_B   Segment1)
   
QuoteNo Type  Cdate Supplier Site ContactPerson Buyer   Created(UserName)



po_headers_all
po_lines_all
mtl_system_items_b
po_vendors
po_vendor_sites_all
po_vendor_contacts
per_all_people_f
fnd_user


Auto Create:
============
It is one of the Purchasing feature to create the RFQ and  PO documents automatically
by using requisition lines.

1)Create Requisition and approve
2)Open the AutoCreate form
3)Select Clear button enter the RequisitionNO
4)Select find button which will shows all the requisition lines
  select the lines whatever we want to include into the RFQ
5)select Action = Create to create new RFQ
    AddTo  to add lines to exisiting to RFQ
6)Select DocumentType = RFQ   
7)select Automatic button which will create RFQ document automatically .

Purchase Order :
================
PO is one of the Main document which will be prepared and approved by the buyer and
send it to the supplier. which contains the following information
terms and Conditions
Items deails
Qty,Price
Distiribution and Shipment Details and so on.

We have four types of Purchase Order 1)STANDARD
         2)PLANNED
         3)BLANKET
         4)CONTRACT 

Purchase Orders=> Purchase Orders
Open the PO form enter the Inforamtion at header level select line level inforamtion
enter the items and quantity,price details
select shippments button enter the shippment details select the Distributions button
enter the Distribution Detauils.
Save
Select the Button called Approve (Uncheck Email Check Box) , Document will be submitted
for approval.
open the Purchase Order summary form  enter PO number Select Find button we can find
the status of the Purchase order.
Goto Tools menu
Action History => We can find who hs submitted for Approve /Reject /Cancel details
Copy Document  => To Create Another PO based on this PO
Control        => To Close the Purchase Order or to cancel the Purchase Order.

  Purchase Order Report
  =====================

POno :   Buyer:
Potype :   Supplier:
ShipTo :   Supplier  Site:
BillTo :   Contact :
Cdate :   Status :             POTotal  :

payment Terms:            Fright Charges:        FOB:    Carrier:

Lineno  Item  Desc  Qty  price  Shipno  ShiptoLoc  ShipToOrg Qty Distno Distqty Requestor
-----   ----  ----  ---  -----  ------   --------  --------- ---  -----  ------ ------ 
=========

Report Questions

Report Questions
1. What is SRW Package?
Ans: The Report builder Built in package know as SRW Package (Sql Report Writer) This package extends reports, Control report execution, output message at runtime, Initialize layout fields, Perform DDL statements used to create or Drop temporary table, Call User Exit, to format width of the columns, to page break the column, to set the colors
Ex: SRW.DO_SQL, It’s like DDL command, we can create table, views, etc,
SRW.SET_FIELD_NUM
SRW. SET_FIELD_CHAR
SRW. SET FIELD _DATE

2. What are Lexical Parameters and bind parameters?
Lexical Parameter is a Simple text string that to replace any part of a SELECT statement. Column names, the from clause, where clause or the order by clause. To create a lexical reference in a query we prefix the parameter name with an ampersand (ex. &.dname,)

3. What is User Parameters?
A parameter, which is created by user. For to restrict values with where clause in select statement.
Data type, width, input mask, initial value, validation trigger, list of values
We can use Lovs in use in user parameter with static and Dynamic Select Statement.
4. What is System Parameters: These are built-in parameters by corporation.
BACKGROUND: Is whether the report should run in the foreground or the background.
COPIES Is the number of report copies that should be made when the report is printed.
CURRENCY Is the symbol for the currency indicator (e.g., "$").

Ans: The Report builder Built in package know as SRW Package (Sql Report Writer) This package extends reports, Control report execution, output message at runtime, Initialize layout fields, Perform DDL statements used to create or Drop temporary table, Call User Exit, to format width of the columns, to page break the column, to set the colors
Ex: SRW.DO_SQL, It’s like DDL command, we can create table, views, etc,
SRW.SET_FIELD_NUM
SRW. SET_FIELD_CHAR
SRW. SET FIELD _DATE
2. What are Lexical Parameters and bind parameters?
Lexical Parameter is a Simple text string that to replace any part of a SELECT statement. Column names, the from clause, where clause or the order by clause. To create a lexical reference in a query we prefix the parameter name with an ampersand (ex. &.dname,)
3. What is User Parameters?
A parameter, which is created by user. For to restrict values with where clause in select statement.
Data type, width, input mask, initial value, validation trigger, list of values
We can use Lovs in use in user parameter with static and Dynamic Select Statement.
4. What is System Parameters: These are built-in parameters by corporation.
BACKGROUND: Is whether the report should run in the foreground or the background.
COPIES Is the number of report copies that should be made when the report is printed.
CURRENCY Is the symbol for the currency indicator (e.g., " $?).
DECIMAL Is the symbol for the decimal indicator (e.g., ".").
DESFORMAT Is the definition of the output device's format (e.g., landscape mode for a printer). This parameter is used when running a report in a character-mode environment, and when sending a bitmap report to a file (e.g. to create PDF or HTML output).
DESNAME Is the name of the output device (e.g., the file name, printer's name, mail userid).
DESTYPE Is the type of device to which to send the report output (screen, file, mail, printer, or
Screen using PostScript format).
MODE Is whether the report should run in character mode or bitmap.
ORIENTATION Is the print direction for the report (landscape, portrait, default).
PRINTJOB Is whether the Print Job dialog box should appear before the report is run.
THOUSANDS Is the symbol for the thousand's indicator (e.g., ",").

5. How many Types of Reports available in Reports
Tabular form-like form – letter Group left
Group above matrix Matrix with group Mailing label
Matrix Report: Simple, Group above, Nested
Simple Matrix Report required 4 groups
1. Cross Product Group
2. Row and Column Group
3. Cell Group
4. Cell column is the source of a cross product summary that becomes the cell content.
Frames: 1.Repeating frame for rows (down direction)
2. Repeating frame for columns (Across)
3. Matrix object the intersection of the two repeating frames

6. What Types of Triggers are Available in Reports.
Report level Triggers
Data Model Triggers
Layout Model Triggers
Report Level Triggers
Before parameter form: If u want take parameters passed to the report and manipulate them so that they appear differently in the parameter form., this is where modification can be done for ex: when u want pass a deptno but show the dname selected , use a before parameter form trigger.
After parameter form & Before Report: These two triggers are fired one after the other. No event occurs in between them. However the way the way that the reports product behaves when the triggers fail is quite different. If the After Parameter trigger fails the report will be put back into the parameter form. It’s useful to place code here to check whether values in your parameter form are valid. Even though the Before Report trigger is executed before the query runs, if this trigger fails it won’t fail until reports tries to display the first page of the report. This means that even if something goes wrong in the before report trigger (meaning that you may not want to run the query at all) It will run anyway
Between pages: This Trigger fires before all pages except first page one. It will not fire after the last page of a report. If a report only has one page it will not fire at all. You can use this trigger to send specific control to the change the paper orientation or to do double sided printing
After report: This trigger fires the report has printed or in the case of a screen report, after the report is closed following viewing. This trigger can be used to update a global variable if u r returning the number of pages in a report. It is also used to delete temporary table used to print the report
Data Model Triggers
Formula Column, Group Filter, Parameter values
Layout Model Triggers

7. What are Format triggers?
Format triggers enable you to modify the display of objects dynamically at run time or to suppress display altogether
For Headings, for repeating frames, for field, for boilerplate object
To format a column based on certain criteria for example
i) To format the max (Sal) for particular department.
ii) To format the Sal column with a Dollar ($) prefix.
iii) To format Date formats….etc

8. What is Data Model?
Data Model is logically group of the Report Objects through query and Data model tools. Once query is compiled report automatically generates group. The queries build the groups ant then Groups are used to populate the report. The only function of queries in report is to create the groups. The Report Editor's Data Model view enables you to define and modify the data model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships. To create the query objects for your data model, you can use the Report Wizard, Data Wizard, or the Query tools in the tool palette.

9. What is Layout model?
Layout Model is to physically arrange Data model group objects on the Report. The Report Editor's Layout Model view enables you to define and modify the layout model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships.

10 What is Live Previewer?
Ans: The Live Previewer is a work area in which you can preview your report and manipulate the actual or live data at the same time. In the Live Previewer you can customize reports interactively, meaning that you can see the results immediately as you make each change.
To activate buttons in the Live Previewer, you must display the report output in the Runtime Previewer. In order to edit your report, such as changing column size, move columns, align columns insert page numbers, edit text, change colors, change fonts set format masks, insert field the Live Previewer must be in Flex Mode.

Access
Title
Viewing region
Rulers
Grid
Toolbar
Style bar
Tool palette
Status bar

11. What is Parameter Form
Ans: The Live Previewer is a work area in which you can preview your report and manipulate the actual or live data at the same time. In the Live Previewer you can customize reports interactively, meaning that you can see the results immediately as you make each change.
To activate buttons in the Live Previewer, you must display the report output in the Runtime Previewer. In order to edit your report, such as changing column size, move columns, align columns insert page numbers, edit text, change colors, change fonts set format masks, insert field the Live Previewer must be in Flex Mode.

Access
Title
Viewing region
Rulers
Grid
Toolbar
Style bar
Tool palette
Status bar
11. What is Parameter Form
Ans: Parameters are variables for report that users can change at runtime immediately prior to the execution of the report. You can use system parameters to specify aspects of report execution, such as the output format, printer name, mailed or number of copies. We can also create own parameters through sql or Pl/sql at runtime.
The Parameter Form view is the work area in which you define the format of the report's Runtime Parameter Form. To do this, you define and modify parameter form objects (fields and boilerplate).
When you run a report, Report Builder uses the Parameter Form view as a template for the Runtime Parameter Form. Fields and boilerplate appear in the Runtime Parameter Form exactly as they appear in the Parameter Form view. If you do not define a Runtime Parameter Form in the Parameter Form view, Report Builder displays a default Parameter Form for you at runtime.

12. What is Query?

The first thing in data model is the query. Through query we access database objects with sql query. Compiled query creates groups. We can create query through query builder, sql query and import query from o/s file or database.

13. What is Group? The first thing in data model is the query. Through query we access database objects with sql query. Compiled query creates groups. We can create query through query builder, sql query and import query from o/s file or database.

13. What is Group?
The first thing in data model is the query. Through query we access database objects with sql query. Compiled query creates groups. We can create query through query builder, sql query and import query from o/s file or database.

13. What is Group?
Ans: Groups are created to organize the columns in your report. When you create a query, Report Builder automatically creates a group that contains the columns selected by the query. You create additional groups to produce break levels in the report, either manually or by using the Report Wizard to create a group above or group left report.

14 What is Repeating Frame?
Ans: Repeating frames surround all of the fields that are created for a group’s columns. Repeating frames correspond to groups in the data model. Each repeating frame must to be associated with a group of data model the repeating frame prints (is fired) once for each record of the group.

15. What is Reference Cursor?
Ans: Repeating frames surround all of the fields that are created for a group’s columns. Repeating frames correspond to groups in the data model. Each repeating frame must to be associated with a group of data model the repeating frame prints (is fired) once for each record of the group.

15. What is Reference Cursor?
A ref cursor query uses PL/SQL to fetch data. Each ref cursor query is associated with a PL/SQL function that returns a strongly typed ref cursor. The function must ensure that the ref cursor is opened and associated with a SELECT statement that has a SELECT list that matches the type of the ref cursor.
You base a query on a ref cursor when you want to:
n more easily administer SQL
n avoid the use of lexical parameters in your reports
n share data sources with other applications, such as Form Builder
n increase control and security
n encapsulate logic within a subprogram
Furthermore, if you use a stored program unit to implement ref cursors, you receive the added benefits that go along with storing your program units in the Oracle database.

16. What is Template?
Ans: Templates define common characteristics and objects that you want to apply to multiple reports. For example, you can define a template that includes the company logo and sets fonts and colors for selected areas of a report. And properties of the objects also
Creation of Template: In Report editor , open a existing Template or Create a new Template and save it concerned directory. Then Edit CAGPREFS.ORA File , and Specify which type of Template are u going to develop.
Ex. Tabular, form, matrix Then give your developed template *.tdf file name.
Develop Report with Newly developed Template.

17 what is Flex mode and Confine mode?
Confine mode
On: child objects cannot be moved outside their enclosing parent objects.
Off: child objects can be moved outside their enclosing parent objects.
Flex mode:
On: parent borders "stretch" when child objects are moved against them.
Ans: Templates define common characteristics and objects that you want to apply to multiple reports. For example, you can define a template that includes the company logo and sets fonts and colors for selected areas of a report. And properties of the objects also
Creation of Template: In Report editor , open a existing Template or Create a new Template and save it concerned directory. Then Edit CAGPREFS.ORA File , and Specify which type of Template are u going to develop.
Ex. Tabular, form, matrix Then give your developed template *.tdf file name.
Develop Report with Newly developed Template.
17 what is Flex mode and Confine mode?
Confine mode
On: child objects cannot be moved outside their enclosing parent objects.
Off: child objects can be moved outside their enclosing parent objects.
Flex mode:
On: parent borders " them.

Off: parent borders remain fixed when child objects are moved against them.

18. What is Page Break?
Ans: To limit the records per page.

19 What is Page Protector?
Ans: The Page Protect property indicates whether to try to keep the entire object and its contents on the same logical page. Setting Page Protect to Yes means that if the contents of the object cannot fit on the current logical page, the object and all of its contents will be moved to the next logical page. Ex: if you set yes, the object information prints another page.
Print Condition
The print condition type First, All, All but first, Last, All but last refer to the frequency with which you want to appear based upon the setting of the print condition object. A print condition object of Enclosing Object is whichever object encloses the current object (could be the parent or a frame within the parent), while Anchoring Object is the parent object (unless you have explicitly anchored the object in which case it is the object to which it is anchored). The key here is that this is about the pages on which the Print Condition Object appears, not the current object. Oracle views First as the first page on which any part of the Print Condition Object is printed, likewise Last is the last page on which any part of the Print Condition Object is printed. For objects inside a repeating frame, this condition is re-evaluated for each instance of the frame.

20 What is Print Direction?
The print condition type First, All, All but first, Last, All but last refer to the frequency with which you want to appear based upon the setting of the print condition object. A print condition object of Enclosing Object is whichever object encloses the current object (could be the parent or a frame within the parent), while Anchoring Object is the parent object (unless you have explicitly anchored the object in which case it is the object to which it is anchored). The key here is that this is about the pages on which the Print Condition Object appears, not the current object. Oracle views First as the first page on which any part of the Print Condition Object is printed, likewise Last is the last page on which any part of the Print Condition Object is printed. For objects inside a repeating frame, this condition is re-evaluated for each instance of the frame.
20 What is Print Direction?
Ans: The Print Direction property is the direction in which successive instances of the repeating frame appear.

21 What is Vertical and Horizental ElacityAns: The Horizontal Elasticity property is how the horizontal size of the object will change at runtime to accommodate the objects or data within it:

22.What is Place holder Columns?
Ans: A placeholder is a column is an empty container at design time. The placeholder can hold a value at run time has been calculated and placed in to It by pl/sql code from anther object. You can set the value of a placeholder column is in a Before Report trigger , A report level formula column(if the place holder column is at report level) A formula column in the place holder group or a group below it
Uses of place holder columns enables u to populate multiple columns from one piece of code. U can calculate several values in one block of pl/sql code in a formula column and assign each value into a different placeholder column. U therefore create and maintain only program unit instead of many.
Store a Temporary value for future reference. EX. Store the current max salary as records are retrieved.

23 What is Formula Column?
Ans: A formula column performs a user-defined computation on another column(s) data, including placeholder columns.

24 What is Summary columns?
Ans: A summary column performs a computation on another column's data. Using the Report Wizard or Data Wizard, you can create the following summaries: sum, average, count, minimum, maximum, % total. You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional summaries: first, last, standard deviation, variance.

25 What is Boilerplate?
Ans: Boilerplate is any text or graphics that appear in a report every time it is run. Report Builder will create one boilerplate object for each label selected in the Report Wizard (it is named B_
Column name). Also, one boilerplate object is sometimes created for each report summary. A boilerplate object is owned by the object surrounding it, unless otherwise noted.

26 What is Data Link
When we join multiple quires in a report the join condition is stored in the data link section
Data links relate the results of multiple queries. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group. When you create a data link in the Data Model view of your report, Report Builder constructs a clause (as specified in the link's Property Palette) that will be added to the child query's SELECT statement at runtime. You can view the SELECT statements for the individual parent and child queries in the Builder, but can not view the SELECT statement that includes the clause created by the data link you define.

27 What is filter and Group Filter
28.What is Query Builder
Ans: A placeholder is a column is an empty container at design time. The placeholder can hold a value at run time has been calculated and placed in to It by pl/sql code from anther object. You can set the value of a placeholder column is in a Before Report trigger , A report level formula column(if the place holder column is at report level) A formula column in the place holder group or a group below it
Uses of place holder columns enables u to populate multiple columns from one piece of code. U can calculate several values in one block of pl/sql code in a formula column and assign each value into a different placeholder column. U therefore create and maintain only program unit instead of many.
Store a Temporary value for future reference. EX. Store the current max salary as records are retrieved.

23 What is Formula Column?
Ans: A formula column performs a user-defined computation on another column(s) data, including placeholder columns.
24 What is Summary columns?
Ans: A summary column performs a computation on another column's data. Using the Report Wizard or Data Wizard, you can create the following summaries: sum, average, count, minimum, maximum, % total. You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional summaries: first, last, standard deviation, variance.
25 What is Boilerplate?
Ans: Boilerplate is any text or graphics that appear in a report every time it is run. Report Builder will create one boilerplate object for each label selected in the Report Wizard (it is named B_
Column name). Also, one boilerplate object is sometimes created for each report summary. A boilerplate object is owned by the object surrounding it, unless otherwise noted.
26 What is Data Link
When we join multiple quires in a report the join condition is stored in the data link section
Data links relate the results of multiple queries. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group. When you create a data link in the Data Model view of your report, Report Builder constructs a clause (as specified in the link's Property Palette) that will be added to the child query's SELECT statement at runtime. You can view the SELECT statements for the individual parent and child queries in the Builder, but can not view the SELECT statement that includes the clause created by the data link you define.
27 What is filter and Group Filter
28.What is Query Builder
Ans: it’s a gui tool to build a query in Report Wizard, Data Wizard or Data model.

29 What is Break Column?
Ans: We can break a column through data model , it Display once for a group

30. How do u call Report from form?
Ans: We can break a column through data model , it Display once for a group
30. How do u call Report from form?
Ans: RUN_PRODUCT and RUN_REPORT_OBJECT

31. HOW CAN U CREATE TWO FORMATS
USING DISTRIBUTION WE CAN CREATE DIFFERENT FORMATS

32 HOW TO DISPLY ONE RECORD PER PAGE ( WHICH PROPERTY WE SHOULD SET)
Set Repeating Frame Properties : Maximum records per page=1 And it will override group filter property.
In Data model Layout , Group Property Through Filter Type & No of records to display
Property, Values are First, last, pl/sql

33. What is Header ,Body, Trailer, and Footer in Reports
Header: The header consists of one or more pages that are printed before report proper. The type of
Information you might want to print title of the page, company logo and address or chart the
Summarizes the report.
Trailer: The trailer consists of one or more pages that print after the report itself, usually used for nothing more than an end of report blank page, but also used for a report summary or chart.
Body: The body is where all the main report objects are placed
Margin: the report layout only governs the part of the pages designated for the main data portion of the report. The margins are can be used to specify page headers and page footers.
34. what are Executable file definitions in Reports
Report Builder (RWBLD60.EXE)
n Reports Runtime (RWRUN60.EXE)
n Reports Convert (RWCON60.EXE)
n Reports Background Engine (RWRBE60.EXE)
n Reports Server (RWMTS60.EXE)
n Reports Web Cartridge (RWOWS60.DLL)
n Reports CGI (RWCGI60.EXE)
n Reports Queue Manager (RWRQM60.EXE)
n Reports Launcher (RWSXC60.EXE)
n Reports ActiveX Control (RWSXA60.OCX)35 what are the Non_query fields?
Aggregated Information, Calculated information, A string Function
Can I highlight and change all the format masks and print conditions of a bunch of fields all at once?
You can. If you highlight a bunch of objects and then right click and select "properties..", Oracle gives you a stacked set of the individual properties forms for each of the selected objects. While this may be useful for some things, it requires changing values individually for each object. However, instead you can select the group of fields and then select "Common properties" from the "Tools" menu which will allow you to set the format mask , print conditions etc. for the whole set of objects at once.

36 How do I change the printed value of a field at runtime?
Triggers are intended to simply provide a true or false return value to determine whether an object should be printed. It is generally not allowed to change any values held in the cursor, make changes to the database, or change the value of it's objects value.
That being said, there is a highly unpublicized method of doing just that using the SRW.Set_Field_Char procedure.
The syntax is SRW.Set_Field_char (0,) and the output of the object that the current trigger is attached to will be replaced by .
There are also SRW.set_fileld_num and SRW.set_field_date for numeric or date fields. While these options do work, they should only be used if a suitable NVL or DECODE statement in the original query is not possible as they are much, much slower to run. Also, note that this change of value only applies to the formatted output. It does not change the value held in the cursor and so can not be used for evaluating summary totals

Report Bursting
The capability of producing multiple copies of a given report or portion of it in different output formats is referred to as report bursting.

Additional Layout:
Additional layout created for to different format using same query and groups without modifying default layout created by report wizard., we can use both layouts according to user requirement.

System Variables as Source Field In Layout Editor
Ans: Current date, Page Number, Panel number, Physical Page Number, Total Pages,
Total Panels, Total Physical Pages.

Link File: Is a special type of boilerplate, that doesn’t have to remain constant for each report run
The type of file contents, can be Text, Image, CGM, Oracle drawing format, or image URL
Source filename: the name of the file the u want link to the report through import Image from
=============================
PURCHASE ORDER QUERY

         PURCHASE ORDER QUERY                                                           
                                                                    
                                            
SELECT PHA.PO_HEADER_ID ,
    pha.SEGMENT1,
    pha.revision_num,
    pha.type_lookup_code,
    trunc(pha.creation_date) Cdate,
    PV.VENDOR_NAME        ,
    PVS.VENDOR_SITE_CODE    ,
    (PVC.last_NAME||','||PVC.firsT_NAME)  ContactName,
    H1.LOCATION_CODE       ShipTO,
    H2.LOCATION_CODE       BillTo,
    pha.currency_code      CUrr,
    PPF.FULL_NAME       Buyer,
    pha.AUTHORIZATION_STATUS     Status,
    pha.COMMENTS        PODesc,
    SUM(pla.quantity*pla.unit_price)         PoTotal
FROM  PO_HEADERS_ALL PHA,
   PO_VENDORS  PV ,
   PO_VENDOR_SITES_ALL PVS,
   PO_VENDOR_CONTACTS  PVC,
   HR_LOCATIONS    H1 ,
   HR_LOCATIONS    H2 ,
   PER_ALL_PEOPLE_F   ppf,
   PO_LINES_ALL    pla
WHERE pha.SEGMENT1         = '3478'
AND   pha.TYPE_LOOKUP_CODE = 'STANDARD'
AND   PV.VENDOR_ID     = PHA.VENDOR_ID
AND   PVS.VENDOR_SITE_ID   = PHA.VENDOR_SITE_ID
AND   PVC.VENDOR_CONTACT_ID= PHA.VENDOR_CONTACT_ID
AND   H1.LOCATION_ID    = PHA.SHIP_TO_LOCATION_ID
AND   H2.LOCATION_ID    = PHA.BILL_TO_LOCATION_ID
AND   ppf.person_id     = PHA.AGENT_ID
AND   pha.po_header_id    = PLA.po_header_id
GROUP BY
pha.SEGMENT1,
pha.revision_num,
pha.type_lookup_code,
trunc(pha.creation_date),
PV.VENDOR_NAME        ,
PVS.VENDOR_SITE_CODE    ,
(PVC.last_NAME||','||PVC.firsT_NAME)  ,
H1.LOCATION_CODE       ,
H2.LOCATION_CODE      ,
pha.currency_code     ,
PPF.FULL_NAME      ,
pha.AUTHORIZATION_STATUS    ,
pha.COMMENTS ,
PHA.PO_HEADER_ID     -   12160


Line Level
===========
SELECT  PLA.LINE_NUM,
  PLT.LINE_TYPE,
  MSI.SEGMENT1  Item,
  (mc.segment1||','||mc.segment2) Category,
  msi.description   Itemdesc,
  MSI.PRIMARY_UOM_CODE UOM,
  pla.quantity,
  pla.unit_price
FROM    PO_LINES_ALL       PLA,
  PO_LINE_TYPES      PLT,
  MTL_SYSTEM_ITEMS_B MSI,
  MTL_CATEGORIES    MC
WHERE   PLA.PO_HEADER_ID      = 12160
AND  PLA.LINE_TYPE_ID      = PLT.LINE_TYPE_ID
AND  MSI.ORGANIZATION_ID   = PLA.ORG_ID
AND  MSI.INVENTORY_ITEM_ID = PLA.ITEM_ID
and  mc.CATEGORY_ID    = pla.CATEGORY_ID




===================
SQL * Loader

SQL * Loader

What is SQL* Loader?

SQL*loader is one of the Oracle tool which will be used to transfer the data from Flat-File to oracle Database table.
We can find the fallowing files in SQL*loader
1. Flat or Data File
2. Control File
3. Bad File
4. Discard File
5. Log File

Flat Or Data File: This file contains the records in a special format; these records will be fetching for other legacy. The extension of these files might be .dat, .txt, or .csv (comma separated view).

Control File: This is SQL loader execution file, which will be used to transfer the date from file to table. In side of these control file, we will mention the Data file path, table name, column mapping. The extension of control file is .ctl
Control File Creation:
Load data
INFILE ‘Data File Path’
INSERT INTO ‘Table Name’
FIELD TERMINATED BY ‘,’
WHERE deptno = 10
TRAILING NULL COLS(column1 , empno
column2, ename
column3, deptno)

Once we develop the control file we will execute this by using fallowing command
C:\> sqlldr user/passward @ Database Control = name of control file (with extension .ctl)This command will start the control file execution, and it will try to read the data and inserting into table. After completion of this execution, automatically three files will gets created
Bad file
Discard file
Log file

Bad File: Bad file contain the records, which are rejected by the SQL*loader. SQL*loader will reject the records, when ever the Flat file format is not correct or if any internal error occurs it will rejected. The extension of bad file is .bad

Discard File: Discard file contains the records which are rejected by the control file, control file reject the records, if record is not satisfying the conditions, which we have mentioned inside of control files the extension of discard file is .dis

Logfile: It contains the complete info of the process, like no of records successfully loaded in to the table
No of records successfully loaded in to the bad file & discard file.
And where the bad, discard file gets created and time taken to complete the process.
Taking the complete log.
LOAD DATA statement is required at the beginning of the control file.
INFILE: INFILE keyword is used to specify location of the datafile or datafiles.
INFILE * specifies that the data is found in the control file and not in an external file. INFILE '$FILE', can be used to send the filepath and filename as a parameter when registered as a concurrent program.
INFILE   '/home/vision/kap/import2.csv' specifies the filepath and the filename.
INTO TABLE is required to identify the table to be loaded into. In the above example INTO TABLE "APPS"."BUDGET", APPS refers to the Schema and BUDGET is the Table name.
FIELDS TERMINATED BY specifies how the data fields are terminated in the datafile.(If the file is Comma delimited or Pipe delimited etc)
OPTIONALLY ENCLOSED BY '"' specifies that data fields may also be enclosed by quotation marks.
TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.


OPTION statement precedes the LOAD DATA statement. The OPTIONS parameter allows you to specify runtime arguments in the control file, rather than on the command line. The following arguments can be specified using the OPTIONS parameter.
SKIP = n -- Number of logical records to skip (Default 0)
LOAD = n -- Number of logical records to load (Default all)
ERRORS = n -- Number of errors to allow (Default 50)
ROWS = n   -- Number of rows in conventional path bind array or between direct path data saves (Default: Conventional Path 64, Direct path all)
BINDSIZE = n -- Size of conventional path bind array in bytes (System-dependent default)
SILENT = {FEEDBACK | ERRORS | DISCARDS | ALL} -- Suppress messages during run
(header, feedback, errors, discards, partitions, all)
DIRECT = {TRUE | FALSE} --Use direct path (Default FALSE)
PARALLEL = {TRUE | FALSE} -- Perform parallel load (Default FALSE)

SQL* Loader Modes:
TYPE OF LOADING:
INSERT   -- If the table you are loading is empty, INSERT can be used.
APPEND  -- If data already exists in the table, SQL*Loader appends the new rows to it. If data doesn't already exist, the new rows are simply loaded.
REPLACE -- All rows in the table are deleted and the new data is loaded
TRUNCATE -- SQL*Loader uses the SQL TRUNCATE command.

C:\> sqlldr userid/passward@Database control=text1.ctl path=direct
SQL* Loader Paths: We can execution SQL* loader in two paths or nodes

Direct

Conventional
By default SQL*loader will be running in conventional mode, if we want to run in direct mode will use the fallowing syntax
C:\> sqlldr userid/passward@Database control=text1.ctl path=direct
Direct mode will disable the table and column constrains and it will insert the data.
Conventional path will check every constrains, if it is satisfied it will insert the record
Conventional path is just like ‘insert statement’

SQL Commands Limitations:to_date, to_char, upper, lower, Initcap, string, decode, nvl
when clause
sequence_name.next_value, Ref-Cursor
sysdate, ltrim, rtrim, constant
Structure of a Control file:
Sample CTL file for loading a Variable record data file:
OPTIONS (SKIP = 1)   --The first row in the data file is skipped without loading
LOAD DATA
INFILE '$FILE'             -- Specify the data file path and name
APPEND                       -- type of loading (INSERT, APPEND, REPLACE, TRUNCATE
INTO TABLE "APPS"."BUDGET"   -- the table to be loaded into
FIELDS TERMINATED BY '|'           -- Specify the delimiter if variable format datafile
OPTIONALLY ENCLOSED BY '"'   --the values of the data fields may be enclosed in "
TRAILING NULLCOLS     -- columns that are not present in the record treated as null
(ITEM_NUMBER    "TRIM(:ITEM_NUMBER)", -- Can use all SQL functions on columns
QTY                 DECIMAL EXTERNAL,
REVENUE             DECIMAL EXTERNAL,
EXT_COST            DECIMAL EXTERNAL TERMINATED BY WHITESPACE "(TRIM(:EXT_COST))"  ,
MONTH           "to_char(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),'DD-MON-YY')" ,
DIVISION_CODE    CONSTANT "AUD"  -- Can specify constant value instead of
Getting value from datafile
)
Skip columns:
You can skip columns using the 'FILLER' option.
Load Data
--
--
--
TRAILING  NULLCOLS
(
name Filler,
Empno ,
sal
)
here the column name will be skipped.


Steps to Run the SQL* LOADER from UNIX:
At the prompt, invoke SQL*Loader as follows:
sqlldr USERID=scott/tiger CONTROL= LOG=
name>
SQL*Loader loads the tables, creates the log file, and returns you to the system prompt. You can check the log file to see the results of running the case study.

Register as concurrent Program:
Place the Control file in $CUSTOM_TOP/bin.
Define the Executable. Give the Execution Method as SQL*LOADER.
Define the Program. Add the Parameter for FILENAME.
=============
INVENTORY QUERY

INVENTORY QUERY

SELECT msi.segment1 AS item_code
, mp.organization_code AS org_code
, decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name) AS subinv_code
, sum(decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.transaction_quantity,sinv.secondary_inventory_name,moq.transaction_quantity,0))AS ohq
FROM
apps.mtl_system_items_b msi
, apps.mtl_onhand_quantities moq
, apps.mtl_parameters mp
, (SELECT msec.secondary_inventory_name,
msec.organization_id
FROM mtl_secondary_inventories msec
WHERE msec.secondary_inventory_name IN ('Sub_INVA','Sub_INVB')) sinv
WHERE AND msi.inventory_item_status_code = 'Active'
AND msi.inventory_item_id = moq.inventory_item_id(+)
AND msi.organization_id = moq.organization_id(+)
AND (moq.subinventory_code IN ('Sub_INVA','Sub_INVB') OR (moq.subinventory_code IS NULL AND sinv.secondary_inventory_name IN ('Sub_INVA','Sub_INVB')))
AND msi.organization_id = mp.organization_id
AND msi.organization_id = sinv.organization_id
GROUP BY msi.segment1,
mp.organization_code,
decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name)
ORDER BY msi.segment1,
decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name),
msi.segment1;


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

SELECT   hou.NAME org, mp.organization_code AS org_code,
            decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name) AS subinventory,
            mil.concatenated_segments LOCATOR, mil.segment1 warehouse_id,
            mil.segment2 product_type, mil.segment3 container,
            mil.segment4 aisle, mil.segment5 bay, mil.segment6 shelf,
            mil.segment7 project, msi.segment1 AS item_code,
            msi.description description, msi.primary_uom_code primary_uom,
            sum(decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.transaction_quantity,sinv.secondary_inventory_name,moq.transaction_quantity,0)) AS ohq,
            moq.lot_number lot_number, mln.expiration_date expire_date,
            cic.item_cost standard_cost, msi.list_price_per_unit,
            mil.attribute1 container_seal, fu1.user_name qty_created_by,
            fu2.user_name qty_last_updated_by, msi.attribute1 rotatable,
            msi.attribute2 slep_eligible, msi.attribute3 categoryi_v,
            TO_NUMBER (msi.attribute4) "Case To Bottle",
            TO_NUMBER (msi.attribute5) "Case To Each",
            TO_NUMBER (msi.attribute6) "Pallet QTY",
            mcst.category_set_name category_name,
            mc.concatenated_segments item_category,
            TO_DATE (SUBSTR (mil.attribute2, 1, 10), 'YYYY/MM/DD') seal_date,
            mil.attribute4 seal_color, mil.attribute3 seal_by,
            element10.element_value "Indications",
            element20.element_value "Dose",
            element30.element_value "Customer",
            element40.element_value "Adult/Ped",
            element50.element_value "Pregnancy Class",
            element60.element_value "Product Identifier",
            element70.element_value "Conv UOM", mln.attribute1 slep_status,
            mc.segment1 agent_type, mc.segment2 administrator,
            mc.segment3 drug_type, mc.segment4 drug_strength,
            mcr1.cross_reference, mcr2.cross_reference, mcr3.cross_reference,
            mcr4.cross_reference, misi.min_minmax_quantity,
            misi.max_minmax_quantity, msi.min_minmax_quantity,
            msi.max_minmax_quantity, mln.attribute2 original_expiration_date,
            mln.attribute3 first_extension_project,
            mln.attribute4 first_extension_date,
            mln.attribute5 second_extension_project,
            mln.attribute6 second_extension_date,
            mln.attribute7 third_extension_project,
            mln.attribute8 third_extension_date,
            mln.attribute9 fourth_extension_project,
            mln.attribute10 fourth_extension_date, msi.organization_id,
            msi.inventory_item_id
       FROM apps.mtl_system_items_b msi,
            apps.mtl_onhand_quantities moq,
            apps.mtl_parameters mp,
            apps.hr_organization_units hou,
            apps.mtl_item_locations_kfv mil,
            apps.mtl_lot_numbers mln,
            apps.cst_item_costs cic,
            apps.fnd_user fu1,
            apps.fnd_user fu2,
            apps.mtl_category_sets_tl mcst,
            apps.mtl_category_sets_b mcs,
            apps.mtl_categories_kfv mc,
            apps.mtl_item_categories mic,
            apps.mtl_descr_element_values element10,
            apps.mtl_descr_element_values element20,
            apps.mtl_descr_element_values element30,
            apps.mtl_descr_element_values element40,
            apps.mtl_descr_element_values element50,
            apps.mtl_descr_element_values element60,
            apps.mtl_descr_element_values element70,
            apps.mtl_cross_references mcr1,
            apps.mtl_cross_references mcr2,
            apps.mtl_cross_references mcr3,
            apps.mtl_cross_references mcr4,
            apps.mtl_item_sub_inventories misi,
            (SELECT msec.secondary_inventory_name, msec.organization_id
               FROM apps.mtl_secondary_inventories msec
              WHERE msec.secondary_inventory_name IN ('SUBIN_A','SUBINV_B')) sinv
      WHERE 1 = 1
        AND hou.organization_id = mp.organization_id
        AND moq.locator_id = mil.inventory_location_id(+)
        AND moq.subinventory_code = mil.subinventory_code(+)
        AND moq.organization_id = mil.organization_id(+)
        AND mil.enabled_flag(+) = 'Y'
        AND msi.inventory_item_status_code = 'Active'
        AND msi.inventory_item_id = moq.inventory_item_id(+)
        AND msi.organization_id = moq.organization_id(+)
        AND mln.lot_number(+) = moq.lot_number
        AND mln.organization_id(+) = moq.organization_id
        AND mln.inventory_item_id(+) = moq.inventory_item_id
        AND msi.inventory_item_id = cic.inventory_item_id(+)
        AND msi.organization_id = cic.organization_id(+)
        AND fu2.user_id(+) = moq.last_updated_by
        AND fu1.user_id(+) = moq.created_by
        AND mc.structure_id = mcs.structure_id
        AND mc.category_id = mic.category_id
        AND mic.inventory_item_id(+) = msi.inventory_item_id
        AND mic.organization_id = msi.organization_id
        AND mic.category_set_id = mcs.category_set_id
        AND mcs.category_set_id = mcst.category_set_id
        AND mcst.category_set_name = 'Inventory'
        AND element10.element_name(+) = 'Indications'
        AND element10.inventory_item_id(+) = msi.inventory_item_id
        AND element20.element_name(+) = 'Dose'
        AND element20.inventory_item_id(+) = msi.inventory_item_id
        AND element30.element_name(+) = 'Customer'
        AND element30.inventory_item_id(+) = msi.inventory_item_id
        AND element40.element_name(+) = 'Adult/Ped'
        AND element40.inventory_item_id(+) = msi.inventory_item_id
        AND element50.element_name(+) = 'Pregnancy Class'
        AND element50.inventory_item_id(+) = msi.inventory_item_id
        AND element60.element_name(+) = 'Product Identifier'
        AND element60.inventory_item_id(+) = msi.inventory_item_id
        AND element70.element_name(+) = 'Conv UOM'
        AND element70.inventory_item_id(+) = msi.inventory_item_id
        AND msi.inventory_item_id = mcr1.inventory_item_id(+)
        AND mcr1.cross_reference_type(+) = 'NDC'
        AND msi.inventory_item_id = mcr2.inventory_item_id(+)
        AND mcr2.cross_reference_type(+) = 'NSN'
        AND msi.inventory_item_id = mcr3.inventory_item_id(+)
        AND mcr3.cross_reference_type(+) = 'NDA'
        AND msi.inventory_item_id = mcr4.inventory_item_id(+)
        AND mcr4.cross_reference_type(+) = 'FMCS'
        AND moq.organization_id = misi.organization_id(+)
        AND moq.inventory_item_id = misi.inventory_item_id(+)
        AND moq.subinventory_code = misi.secondary_inventory(+)
        AND (moq.subinventory_code IN ('SUBIN_A','SUBINV_B') OR (moq.subinventory_code IS NULL AND sinv.secondary_inventory_name IN ('SUBIN_A','SUBINV_B')))
        AND msi.organization_id = mp.organization_id
        AND msi.organization_id = sinv.organization_id
   GROUP BY hou.NAME,
            msi.segment1,
            mp.organization_code,
            decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name),
            mil.concatenated_segments,
            mil.segment1,
            mil.segment2,
            mil.segment3,
            mil.segment4,
            mil.segment5,
            mil.segment6,
            mil.segment7,
            msi.description,
            msi.primary_uom_code,
            moq.lot_number,
            mln.expiration_date,
            cic.item_cost,
            msi.list_price_per_unit,
            mil.attribute1,
            fu1.user_name,
            fu2.user_name,
            msi.attribute1,
            msi.attribute2,
            msi.attribute3,
            TO_NUMBER (msi.attribute4),
            TO_NUMBER (msi.attribute5),
            TO_NUMBER (msi.attribute6),
            mcst.category_set_name,
            mc.concatenated_segments,
            mil.attribute2,
            mil.attribute3,
            mil.attribute4,
            element10.element_value,
            element20.element_value,
            element30.element_value,
            element40.element_value,
            element50.element_value,
            element60.element_value,
            element70.element_value,
            mln.attribute1,
            mc.segment1,
            mc.segment2,
            mc.segment3,
            mc.segment4,
            mcr1.cross_reference,
            mcr2.cross_reference,
            mcr3.cross_reference,
            mcr4.cross_reference,
            misi.min_minmax_quantity,
            misi.max_minmax_quantity,
            msi.min_minmax_quantity,
            msi.max_minmax_quantity,
            mln.attribute2,
            mln.attribute3,
            mln.attribute4,
            mln.attribute5,
            mln.attribute6,
            mln.attribute7,
            mln.attribute8,
            mln.attribute9,
            mln.attribute10,
            msi.organization_id,
            msi.inventory_item_id
   ORDER BY 4;
=====================

No comments:

Post a Comment