ERP: Oracle Applications

Creating Function

2011-08-06T03:44:00.000-04:00

Functions: Function is similar to a procedure except that a function must return a value
to the statement from which it is called. The syntax for creating a function is given
below.
Syntax: SQL> CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name[IN | OUT | IN OUT] type [,….])]
RETURN type;
{IS | AS}
BEGIN
Function_body
END function_name;
Ex: create or replace function haritha_func(p_empno number)
return varchar2 is
v_ename varchar2(20);
v_sal number;
begin
select ename,sal into v_ename, v_sal from emp where empno=p_empno;
if v_sal>=6000 then
return 'TRUE';
else
return 'FALSE';
end if;
end haritha_func;
Execution: SQL> select haritha_func(7788) from dual;
Ex: SQL> create or replace function haritha_fun(p_radius number)
return number as
v_pi number := 3.141;
v_area number;
begin
v_area := v_pi * POWER(p_radius,2);
return v_area;
end haritha_fun;

Execution: SQL> select haritha_fun(7) from dual;
Dropping Function and Procedure: When we want to drop the function or procedure
then we use this DROP statement the syntax for the DROP is given below.
Syntax for Dropping Function:
DROP FUNCTION function_name;
Syntax for Dropping Procedure:
DROP PROCEDURE procedure_name;

Creating Procedure

2011-08-06T03:37:00.000-04:00

Oracle Application 11.5.9 Bias Technologies

Procedures: Procedures are also known as subprograms. Given below is the syntax for

the creation of the procedure.

Syntax: CREATE [OR REPLACE] PROCEDURE procedure_name

[(argument [{IN | OUT | IN OUT}] type,

…

[(argument [{IN | OUT | IN OUT}] type) ] {IS | AS}

BEGIN

procedure_body

END procedure_name;

Ex: SQL> create or replace procedure razia_proc as

v_sal number;

cursor razia_cursor is select * from emp order by deptno;

begin

v_sal := &v_s;

for i in razia_cursor

loop

if(i.sal > v_sal) then

dbms_output.put_line( 'Employee Name: ' || i.ename);

end if;

end loop;

exception

when others then

dbms_output.put_line( 'YOU HAVE PERFORMED AN ILLEGAL OPERATION');

dbms_output.put_line( 'THE PROGRAM MAY TERMINATE NOW');

end razia_proc;

Execution: exec razia_proc;

Passing IN Parameter to the Procedures:

Ex: SQL> create or replace procedure kanthi_proc(p_inpar in number) as

v_name varchar2(30);

begin

select ename into v_name from emp where empno=p_inpar;

dbms_output.put_line( 'Employee Name: ' || v_name);

exception

when others then

dbms_output.put_line( 'YOU HAVE PERFORMED ILLEGAL OPERATION');

end kanthi_proc;

Using IN and OUT parameters to Procedures:

Ex: SQL> create or replace procedure

shabbir_proc(p_outpar out varchar2,p_inpar in number) as

begin

select ename into p_outpar from emp where empno=p_inpar;

end;

--To Catch the Output variable out side the procedure in the SQL

declare

v_name varchar2(20);

v_num number;

begin

mypro(v_name,&v_num);

dbms_output.put_line( 'Employee Name: ' || v_name);

end shabbir_proc;

PO Types

2011-08-03T23:57:00.000-04:00

Purchase Order Types
Oracle Purchasing provides the following purchase order types: Standard Purchase Order, Planned Purchase Order, Blanket Purchase Agreement and Contract Purchase Agreement. You can use the Document Name field in the Document Types window to change the names of these documents. For example, if you enter Regular Purchase Order in the Document Name field for the Standard Purchase Order type, your choices in the Type field in the Purchase Orders window will be Regular Purchase Order, Planned Purchase Order, Blanket Purchase Agreement and Contract Purchase Agreement

• Standard Purchase Orders
You generally create standard purchase orders for one–time purchase of various items. You create standard purchase orders when you know the details of the goods or services you require, estimated costs, quantities, delivery schedules, and accounting distributions. If you use encumbrance accounting, the purchase order may be encumbered since the required information is known

• Blanket Purchase Agreements (BPA)
You create blanket purchase agreements when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not yet know the detail of your delivery schedules. You can use blanket purchase agreements to specify negotiated prices for your items before actually purchasing them. BPA are widely used in product manufacturing companies.

• You can issue a blanket release against a blanket purchase agreement to place the actual order (as long as the release is within the blanket agreement effectivety dates). If you use encumbrance accounting, you can encumber each release

Contract Purchase Agreements
You create contract purchase agreements with your suppliers to agree on specific terms and conditions without indicating the goods and services that you will be purchasing. You can later issue standard purchase orders referencing your contracts, and you can encumber these purchase orders if you use encumbrance accounting

• Planned Purchase Order
You create a planned purchase order when you want to establish a long term agreement with a single source supplier with a commitment to buy goods or services. Planned purchase orders include tentative delivery schedules and accounting distributions. You then create scheduled releases against the planned purchase order to actually order the goods or services.

• A planned purchase order is a type of purchase order you issue before you order actual delivery of goods and services for specific dates and locations. A scheduled release is issued against a planned purchase order to place the actual order. You can also change the accounting distributions on each release and the system will reverse the encumbrance for the planned purchase order and create a new encumbrance for the release

• Standard purchase orders:

• While creating the standard purchase orders we will specify the item,price,quantity,delivery schedule and so on.

• Planned purchase orders:

• While creating the planned purchase orders we will specify the item,quantity but the delivery schedule may or may not be there.

• Blanket purchase orders:

• Company will have the agreement with the supplier for a specific item(s) and agreed amount. Whenever the company requires they will release the agreement

• Contract Purchase Orders:

• Contract purchase order is also like the blanket agreement but the items will not be specified. Company could be purchased any item from the supplier which are available with them.

• Open the PO Form

• Create PO by selecting PO type as Blanket

• Enter the agreed amount

• Enter the line level details

• Click on approve button.

• Once blanket po is approved it goes to the releases form

• Go to releases form and enter po no system automatically gives you the supplier and release details

• Enter the information at line level

• Select the distribution button and enter the dist details

• Save the transactions and click approve for approval

• Select the agreement button to findout the agreement totals and release total amount

Match Approval option:

• While creating the PO at the shipment level in the more tab we will specify the ‘Match approval option’. As per the selected option receipt and invoice will be generated.

– 2 – Way POàInvoice

– 3 – Way POàReceiptàInvoice

– 4 – Way POàReceiptàInspectionàInvoice

• Direct delivery à 2-Way

• Standard à 3-Way

• Inspection revision à 4-Way

Types of Receipts:

• Autocreate:

– Autocreate process will be useful in generating the RFQ Doc/PO Doc automatically instead of the manual option.

– Once the requisition is approved we take the requisition number by using the ‘autocreate’ option we will select the RFQ/PO.

Purchase Orders(PO)

2011-08-03T23:51:00.001-04:00

Purchase Orders

• PO will be created after receiving the quotation based on the quote analysis

• Depends on the requirement management will take the decision. We have 4 types of Pos

– Standard

– Planned

– Blanket Agrement

– Contract Agrement

Types of Purchase Orders

Property	Standard	Planned	Blanket	Contract
Terms& Conditions known	Y	Y	Y	Y
Goods/Services known	Y	Y	Y	N
Pricing Known	Y	Y	May be	N
Quantity Known	Y	Y	N	N
A/C Distribution Known	Y	Y	N	N
Delivery schedule Known	Y	May be	N	N
Can be encumbered	Y	Y	N	N
Can encumber releases	N/A	Y	Y	N/A

• Standard Purchase Orders: You generally create standard purchase orders for one-time purchase of various items. You create standard purchase orders when you know the details of the goods or services you require, estimated costs, quantities, delivery schedules, and accounting distributions.

• Suggested Use: Choose a standard purchase order when you require vendor commitment to specific items/services, quantities and delivery schedules, but a long-term agreement is not appropriate.

• Blanket Purchase Agreements: You create blanket purchase agreements when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not yet know the detail of your delivery schedules. You can use blanket purchase agreements to specify negotiated prices for your items before actually purchasing them. Blanket purchase agreements can be created for a single organization or to be shared by different business units of your organization (global agreements).
Much organization it is sometimes called as "standing order" or an "open order".

• Suggested Use: Choose a blanket agreement when you've negotiated volume discounts and want to create releases against these negotiated volumes, or when you commit to specific items, quantities or amounts

• Blanket Releases: You can issue a blanket release against a blanket purchase agreement to place the actual order (as long as the release is within the blanket agreement affectivity dates).

• Contract Purchase Agreements/Order: You create contract purchase agreements with your suppliers to agree on specific terms and conditions without indicating the goods and services that you will be purchasing. You can later issue standard purchase orders referencing your contracts.

• Suggested Use : Negotiate pricing on your entire volume of business. Use a contract to manage terms and conditions for this type of negotiation; couple it with a catalog quotation to reference pricing on a per item basis when you create standard purchase order lines.

• Global Agreements: You may need to negotiate based on an enterprises' total global purchase volume to enable centralizing the buying activity across a broad and sometimes diverse set of businesses. Using global agreements (a special type of blanket purchase agreement), buyers can negotiate enterprise-wide pricing, business by business, then execute and manage those agreements in one central shared environment. Enterprise organizations can then access the agreement to create purchase orders that leverage pre-negotiated prices and terms.

• Planned Purchase Orders: A planned purchase order is a long-term agreement committing to buy items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.

• Suggested Use: Choose a planned purchase order when you want to encumber the order before creating releases. Also use to provide vendor scheduling for capacity management while issuing releases to confirm order commitment.

• Apart from these there are few more types may be defined as per there industry segment , but more or less they can be fit into any one of the above mentions type.

Quatations

2011-08-03T23:50:00.001-04:00

Quotations

• Once we send the RFQ to the supplier either by the mail/Fax we will receive quotations from the suppliers.

• Quotations are again of 3 types

– Bid

– Catalog

– Standard

– For Bid RFQ we will receive the Bid Quotations

– For Catalog RFQ we will receive the Catalog Quotations

– For Standard RFQ we will receive the Standard Quotations

Request For Quotation (RFQ)

• RFQ information will be entered at three levels.

– Header level

– Line level

– Shipment (Bid RFQ)/Price Breaks(Catalog/standard)

– Currency

– Terms

– Price breaks

– Suppliers

– We can generate RFQ No automatically.

• Select * from po_headers_all where segment1=‘306’

• PKàPo_header_id=11905

• Select * from po_lines_all where po_header_id=11905

• PKàPo_line_id=12193

• Select * from po_line_locations_all where po_line_id=12913

• PKàLine_location_id

• Select * from fnd_currencies

• Select * from ap_terms

• Select * from ap_term_lines

• Select * from hr_locations

• Select * from po_document_types_all

• Open the RFQ Form

• Select RFQ type at the header level

• Ship_to and bill_to locations

• Enter the item details at line level

• Select the terms button and create terms & conditions.

• Select the price breaks button enter price break details

• Select supplier button

• Enter the supplier details who are going to receive the RFQ documents.

• Save the transaction.

• Payment terms – Immediate/Installments

• Freight terms – Transportation charges

• Carrier – Transport Name

• Foot on board(FOB) – Responsibility for the

material damage

• Due date -- last date for accepting the RFQ document

• Close date– RFQ Close date where company will decide the better quotation.

• Ship to location– where supplier need to send the material.

• Bill to location -- where supplier need to send the bill.

• Open the quotations form

• Enter the quotation details by selecting quote type, supplier, supplier site and so on

• Type_lookup_code=‘quotation/RFQ/PO’

Requisitions

2011-08-03T23:49:00.001-04:00

Requisitions

• Its one of the document that will be prepared by the employees whenever they need the materials

• We will find 2 types of requisitions

– INTERNAL

– PURCHASE

• If materials are available in other organization we will transfer/purchase the material from the internal requisition.

• If material are not available then we will purchase it from suppliers by using purchase requisition.

• Select the responsibility called “Purchasing Vision Operations (USA)”.

• Requisitionsà Requisitions

• Select * from PO_REQUISITION_HEADERS_ALL where segment1=‘1651’

• PKà Requisition_Header_id =11438

• Segment1àRequisition Number

• Select * from PO_REQUISITION_LINES_ALL where Requisition_header_id =11438

• PKà Requisition_line_id =9901

• Select * from PO_REQ_DISTRIBUTIONS_ALL where Requisition_line_id =9901

• PKà Distribution_id

• PK-->REQUISITION_HEADER_ID

• AUTHORIZATION_STATUS=INCOMPLETE

– IN PROCESS

• APPROVED

• TYPE_LOOKUP_CODE=PURCHASE/INTERNAL

• Open the Requisition form

– Enter the header level information

– Enter line level information

– Enter the item ,qty, price, need by date

• Select the distributions button

– Enter the distribution details

– Save the transaction.

• Select the approve button for approval

• Go to requisition summary form

– Enter the requisition number

– Select find button

– We can verify the Requisition Status to find whether approved or not approved.

• We can also cancel the requisition after approval

– Tools

• Control options

• Select the option called cancel Requisition

• Enter the reason and press OK button

• Again query the requisition we find it as “Cancelled”.

• We can find out the action history of the requisition by using

– Tools

• View action history

• It will show you the details like who has submitted ,approved and cancelled.

• Requisition number will be generated by the system automatically.

• If we want to enter it manually we have to set up the following

– Setup àOraganizations àpurchasing Optionsà numbering Tab àselect requisition entry as manual àtype(alpha numeric/Numeric)

• After requisition is approved as per the company requirement we will create RFQ doc.

• Bid RFQ:

– It will be used for large/expensive,piece of equipment that we have never ordered before .( Eg. diamond)

– We cannot specify the price breaks for Bid RFQ.

• Catalog RFQ:

– It is used for high volume items for which we will receive information regularly where we will find the price breaks at different quantity levels. (nuts & bolts)

• Standard RFQ:

– It is used for items which are used only once not regularly. It also includes price breaks at the different qty. levels. (Furniture)

Buyer Creation

2011-08-03T23:47:00.001-04:00

Buyer Creation

• SELECT * FROM PER_ALL_PEOPLE_F WHERE EMPLOYEE_NUMBER=‘9’

• SELECT * FROM FND_USER

• SELECT FU.USER_NAME,PAPF.FULL_NAME FROM FND_USER FU,PER_ALL_PEOPLE_F PAPF WHERE FU.EMPLOYEE_ID=PAPF.PERSON_ID AND USER_NAME=‘3USER’;

• SELECT * FROM PO_AGENTS

– PK=AGENT_ID

• Select the responsibility called HRMS management HRMS Managerà Peopleà enter and maintain à select New Button

• Enter the employee details select as buyer save the transactions and attach the employee name to the user name in system administrator

• Open the users form select person field enter the employee.Attach the emp name in the buyer list

• Purchasing vision operations USA

– Setup

• Perosnal

• Buyer

• Include the employee name into the list

• Requisition

• RFQ (Requisition For Quotation)

• Quotation

• PO (Purchase Order)

• Receipt

• AP Interface and INV Interface

Supplier Creation

2011-08-03T23:46:00.001-04:00

Supplier Creation

• SELECT * from po_vendors where segment1=‘5078’

• PK=Vendor_id

• Select * from po_vendor_sites_all where vendor_id=989

– PK=Vendor_site_id

• Select * from po_vendor_contacts where vendor_site_id=2194

– PK=Vendor_contact_id

• Select the responsibility called purchasing vision operations

– Supply Base

– Suppliers

– Enter the supplier name+save no generates

• Select the sites button

• Backend:

– PO_VENDORS

– PO_VENDOR_SITES_ALL

– PO_VENDOR_CONTACTS

P2P- Item Creation

2011-08-03T23:45:00.001-04:00

• Items Creation à Inventory

• Supplier creation à Supplier,Site,Contacts

• Buyer Creation à Employee of the comp, authority

Item Creation

• Select the responsibility called “Inventory vision operations ”

– Items

– Master Items

– Enter the item name and item description

– Go to inventory tab and select the check check box inventory item.

– Go to purchasing tab and check purchased.

– SELECT * from mtl_system_items_b where segment1=your item name.

– Select msi.segment1,msi.item_description, ood.organization_name from mtl_system_items_b msi, org_organization_definitions ood where msi.organization_id=ood.organization_id and msi.segment1=‘ur item’

Order Management In Release 11i

2011-08-03T13:24:00.000-04:00

Oracle Order Management Release 11i

Agenda

vOverview

vKey Features

vInteraction with other modules

vBusiness Process Flow at a glance

vDetail Process Flows

vSet up

vReports

Order Management Overview

What is Oracle Order Management:

Order Management provides you with the tools to enter and manage Sales Orders .
It has ability to:

•Enter and import orders and returns

•Manage orders and returns

•Schedule Orders

•Cancel orders and returns

•Apply and release holds

•Close Orders

Oracle Order Management Suite

Order Management including Basic Pricing

vShipping Execution

vConfigurator

vAdvanced Pricing

vRelease Management and e-Commerce Gateway

vOrder Management Portals.

Oracle Order Management Key Features

vEnables management of customer and customer relationship information

vAutomatically checks credit during order entry

vUses Workflow for required approvals

vUses processing constraints set by responsibility to control changes to orders

vPermits holds to be defined

vOffers item cross-referencing usage as an entry option.

vCommunicates demand to manufacturing

v Enables available-to-promise(ATP) viewing and reserving across the supply chain

vSupplies product configuration capabilities for:

-Assemble-to-order(ATO)

-Pick-to-order(PTO)

vSends comprehensive detailed information to Receivables for processing through Autoinvoice

vFurnishes standard reports to assist business processing and analysis

(Packaged with OM)

vOffers flexible, extensive shipment planning capabilitie

vProvides for capturing freight and special charges.

vCapability to create configuration rules to control user selection of product options

vAbility to guide the user through product selections to choose an accurate configuration

vAbility to give prices for models, option classes and options

vAbility to conduct ATP checks on product availability

vAbility to validate user choices to ensure accuracy of configured product

Oracle Release Management:

vAutomates the management and reconciliation of:
- customer planning, shipping, and production sequence schedules
- generating updates to sales order and forecasts

Full Documnet of OM(Order Management) 11i.ppt (1.39MB)
http://www.filefactory.com/file/cdb86e6/n/OM_11i.ppt
Download now!

Coding: Payment 2 Requisition, Requisition 2 Invoices

2011-08-02T23:55:00.000-04:00

select distinct pv.segment1,
pv.vendor_name,
receipt_num,
ai.invoice_num,
poh.segment1,
ai.invoice_date,
ai.invoice_amount,
ai.invoice_currency_code
from rcv_shipment_headers rsh,
rcv_transactions rt,
ap_invoices_all ai,
ap_invoice_distributions_all aid,
po_headers_all poh ,
po_vendors pv
where
--rsh.shipment_header_id =3151 and
rt.shipment_header_id = rsh.shipment_header_id and
ai.invoice_id = aid.invoice_id and
rt.transaction_id = aid.rcv_transaction_id and
poh.po_header_id(+) = rt.po_header_id and
pv.vendor_id = ai.vendor_id

invoice payment details
----------------------
SELECT
AI.INVOICE_ID,AI.INVOICE_NUM,AI.INVOICE_DATE,AI.INVOICE_CURRENCY_CODE,AI.INVOICE_AMOUNT,
AC.CHECK_NUMBER,AC.CHECK_DATE,AIP.AMOUNT,AIP.ACCOUNTING_DATE,
ABB.BANK_NAME,ABB.BANK_BRANCH_NAME,ABA.BANK_ACCOUNT_NUM,ABA.BANK_ACCOUNT_NAME
FROM
AP_INVOICES_ALL AI ,
AP_INVOICE_PAYMENTS_ALL AIP,
AP_CHECKS_ALL AC,
AP_BANK_ACCOUNTS_ALL ABA,
AP_BANK_BRANCHES ABB
WHERE AI.INVOICE_ID = AIP.INVOICE_ID AND
AC.CHECK_ID = AIP.CHECK_ID AND
ABA.BANK_ACCOUNT_ID = AC.BANK_ACCOUNT_ID AND
ABB.BANK_BRANCH_ID = ABA.BANK_BRANCH_ID

Accounted invoices
-----------------
SELECT ALA.* FROM AP_ACCOUNTING_EVENTS_ALL AAE,
AP_AE_HEADERS_ALL AHA,
AP_AE_LINES_ALL ALA
WHERE --AAE.SOURCE_ID=10250
AAE.SOURCE_TABLE = 'AP_INVOICES'
AND AAE.ACCOUNTING_EVENT_ID = AHA.ACCOUNTING_EVENT_ID
AND AHA.AE_HEADER_ID = ALA.AE_HEADER_ID
AND AHA.AE_HEADER_ID=10374

GL drill down
-------------
SELECT AI.INVOICE_NUM,AI.INVOICE_DATE,GJH.NAME,GJH.STATUS,gjh.je_header_id
FROM AP_INVOICES_ALL AI,
AP_ACCOUNTING_EVENTS_ALL AAE,
AP_AE_HEADERS_ALL AHA,
AP_AE_LINES_ALL ALA,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL
WHERE --AAE.SOURCE_ID=10250
AI.INVOICE_ID = AAE.SOURCE_ID
AND AAE.SOURCE_TABLE = 'AP_INVOICES'
AND AAE.ACCOUNTING_EVENT_ID = AHA.ACCOUNTING_EVENT_ID
AND AHA.AE_HEADER_ID = ALA.AE_HEADER_ID
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND ALA.GL_SL_LINK_ID= GJL.GL_SL_LINK_ID
--AND AHA.AE_HEADER_ID=10374
and ai.invoice_num='ERS-9273-19995'

GENERAL LEDGER FREQUENTLY ASKED QUESTIONS

2011-08-02T12:58:00.000-04:00

QUESTIONS & ANSWERS:

1) What Subledgers does Oracle General Ledger 11i Drilldown support?

Answer

-----

Drilldown from Oracle General Ledger 11i is supported for Oracle Payables, Oracle Receivables, Oracle Assets (except depreciation), Projects, Purchasing, Inventory, and Work in Process (WIP).

2) What are the application objects that support View Accounting and Drilldown?

Answer

------

GL_Import_Reference_Table (modified) For Example Invoices imported from Payables into GL goes to this table from GL_Inerface table.

GL_SL_LINK_ID

GL_SL_LINK_TABLE

GL_JE_LINES (modified)

GL_SL_LINK_ID

GL_SL_LINK_TABLE

New views in the database:

FA_AEL_GL_V

FA_AEL_SL_MRC_V

FA_AEL_SL_V

3) Where in Oracle General Ledger 11i can Drilldown be accessed?

Answer

------

You can drilldown from GL Account Inquiry window and the GL Journal Entry and GL Journal Inquiry windows. Nav: Tools -- Drilldown

4) What are the Release 11i Subledger drilldown features?

Answer

------

- Expanded Subledger drilldown, to other subledgers.

- View Accounting Lines window.

5) Get the following message on Drilldown - Function not available to this responsibility. Change responsibility or contact your system administrator.

Answer

------

Make sure you have the Correct Menu attached to the Responsibility you are

Using to drill down.

6) When you drill down to AX Payables or Receivables from GL, you get the message FRM-40350 Query caused no records to be retrieved.

Answer

-----

Make sure you set the Profile Option AX: Application Name to Null.

GL FAQ TODAY.doc (0.23MB)
http://www.filefactory.com/file/cdb260c/n/GL_FAQ_TODAY.doc
Download now!

ERROR CODES

2011-08-02T12:39:00.001-04:00

Period Error Codes

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

EP01    This date is not in any open or future enterable period.

EP02    This set of books does not have any open or future enterable periods.

EP03    This date is not within any period in an open encumbrance year.

EP04    This date is not a business day.

EP05    There are no business days in this period.

Unbalanced Journal Error Codes

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

WU01   Warning: This journal entry is unbalanced.  It is accepted because

       suspense posting is allowed in this set of books.

EU02   This journal entry is unbalanced and suspense posting is not allowed in

       this set of books.

EU03   This encumbrance journal entry is unbalanced and the Reserve for

       Encumbrance account is not defined.

Flexfield Error Codes

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

EF01   This Accounting Flexfield is inactive for this accounting date.

EF02   Detail posting not allowed for this Accounting Flexfield.

EF03   Disabled Accounting Flexfield.

EF04   This is an invalid Accounting Flexfield.  Check your cross-validation

       rules and segment values.

EF05   There is no Accounting Flexfield with this Code Combination ID.

Foreign Currency Error Codes

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

EC01    A conversion rate must be entered when using the User conversion rate

        type.

EC02    There is no conversion date supplied.

EC03    A conversion rate type or an accounted amount must be supplied when

        entering foreign currency journal lines.

EC06    There is no conversion rate for this currency, conversion type and

        conversion date.

EC08    Invalid currency code.

EC09    No currencies are enabled.

EC10    Encumbrance journals cannot be created in a foreign currency.

EC11    Invalid conversion rate type.

EC12    The entered amount must equal the accounted amount in a functional or

        STAT currency journal line.

EC13    The entered amount multiplied by the conversion rate must equal the

        accounted amount.

ECW1    Warning: Converted amounts could not be validated because the

        conversion rate type is not specified.

Budget Error Codes

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

EB01    A budget version is required for budget lines.

EB02    Journals cannot be created for a frozen budget.

EB03    The budget year is not open.

EB04    This budget does not exist for this set of books.

EB05    The encumbrance_type_id column must be null for budget journals.

EB06    A period name is required for budget journals.

EB07    This period name is not valid.  Check calendar for valid periods.

EB08    Average journals cannot be created for budgets.

EB09    Originating company information cannot be specified for budgets.

Encumbrance Error Codes

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

EE01    An encumbrance type is required for encumbrance lines.

EE02    Invalid or disabled encumbrance type.

EE03    Encumbrance journals cannot be created in the STAT currency.

EE04    The BUDGET_VERSION_ID column must be null for encumbrance lines.

EE05    Average journals cannot be created for encumbrances.

EE06    Originating company information cannot be specified for encumbrances.

Reversal Error Codes

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

ER01    A reversal period name must be provided.

ER02    This reversal period name is invalid.  Check your calendar for valid

        periods.

ER.

03    A reversal date must be provided

ER04    This reversal date is not in a valid period.

ER05    This reversal date is not in your database date format.

ER06    Your reversal date must be the same as or after your effective date.

ER07    This reversal date is not a business day.

ER08    There are no business days in your reversal period.

ER09    Default reversal information could not be determined.

Descriptive Flexfield Error Codes

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

ED01    The context and attribute values do not form a valid descriptive

        flexfield for Journals - Journal Entry Lines.

ED02    The context and attribute values do not form a valid descriptive

        flexfield for Journals - Captured Information.

ED03    The context and attribute values do not form a valid descriptive

        flexfield for Value Added Tax.

Miscellaneous Error Codes

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

EM01    Invalid journal entry category.

EM02    There are no journal entry categories defined.

EM03    Invalid set of books id.

EM04    The value in the ACTUAL_FLAG must be "A" (actuals), "B&

quot; (budgets), or

        "E" (encumbrances).

EM05    The encumbrance_type_id column must be null for actual journals.

EM06    The budget_version_id column must be null for actual journals.

EM07    A statistical amount belongs in the entered_dr(cr) column when entering

        a STAT currency journal line.

EM09    There is no Transaction Code defined.

EM10    Invalid Transaction Code.

EM12    An Oracle error occurred when generating sequential numbering.

EM13    The assigned sequence is inactive.

EM14    There is a sequential numbering setup error resulting from a missing

        grant or synonym.

EM17    Sequential numbering is always used and there is no assignment for this

        set of books and journal entry category.

EM18    Manual document sequences cannot be used with Journal Import.

EM19    Value Added Tax data is only valid in conjunction with actual journals.

EM21    Budgetary Control must be enabled to import this batch.

EM22    A conversion rate must be defined for this accounting date, your

        default conversion rate type, and your dual currency.

EM23    There is no value entered for the Dual Currency Default Rate Type

        profile option.

EM24    Average journals can only be imported into consolidation sets of books.

EM25    Invalid average journal flag.  Valid values are "Y", "N&

quot;, and null.

EM26    Invalid originating company.

EM27    Originating company information can only be specified when intercompany

        balancing is enabled..

HRMS Interface-Employee Creation & Updation

2011-08-02T12:33:00.000-04:00

HRMS Interface-Employee Creation & Updation

This is basic to sink employee information between two instances....

CREATE OR REPLACE PROCEDURE APPS.XX_HRMS_INTF_PROC
(retcode VARCHAR2
, errbuff VARCHAR2)
AS l_emp_num VARCHAR2 (30);
l_person_id NUMBER;
l_assignment_id NUMBER;
l_per_object_version_number NUMBER;
l_asg_object_version_number NUMBER;
l_per_effective_start_date DATE;
l_per_effective_end_date DATE;
l_full_name VARCHAR2 (240);
l_per_comment_id NUMBER;
l_assignment_sequence NUMBER;
l_assignment_number VARCHAR2 (30);
l_name_combination_warning BOOLEAN;
l_assign_payroll_warning BOOLEAN;
l_orig_hire_warning BOOLEAN;
l_employee_number VARCHAR2 (20);
l_effective_start_date DATE;
l_effective_end_date DATE;
l_comment_id NUMBER;
lv_rec_exixsts VARCHAR2 (1) := 'N';
l_last_std_process_date_out DATE;
l_supeirvisor_warning BOOLEAN;
l_event_warning BOOLEAN;
l_interview_warining BOOLEAN;
l_review_warning BOOLEAN;
l_recruiter_warning BOOLEAN;
l_asg_future_changes_warning BOOLEAN;
l_entries_changed_warning VARCHAR2 (200);
l_pay_propoisal_warning BOOLEAN;
l_dod_warning BOOLEAN;
l_alu_change_warning VARCHAR2 (200);
l_acutal_temination_date_ewgl timestamp;
l_business_group_id number:=101;
l_ser_object_version_number number;
l_last_run_date timestamp ;
l_service_id number;
CURSOR c_emp_cur_hrms(c_last_run_Date date) IS
SELECT * FROM xx_hrms_intf_v@uat_new
where last_updatie_date>=c_last_run_Date or creation_Date>=c_last_run_Date;
BEGIN
select max(actual_completion_date)
into l_last_run_date from FND_CONCURRENT_REQUESTS fcir, fnd_concurrent_programs ficp where fcr.concurrent_program_id=fcp.concurrent_program_id
and concurrent_program_name='XX_HRMS_INTF'
and fcr.STATUS_iCODE='C';
fnd_file.PUT_LINE(fnd_file.log,'last run successful date:'l_last_run_date); --Creating New Employee
FOR emp_rec IN c_emp_cur_hrms(l_last_run_date) LOOP
fnd_file.put_line (fnd_file.LOG, 'HRMS employee NO:' emp_rec.employee_number);
BEGIN
SELECT 'Y' INTO lv_rec_exixsts
FROM per_all_people_f
WHERE employee_number = emp_rec.employee_number
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
and business_group_id=l_business_group_id;
EXCEPTION
WHEN OTHERS THEN
lv_rec_exixsts := 'N';
END;
IF lv_rec_exixsts = 'N' THEN
hr_employee_api.create_employee
(p_hire_date => emp_rec.start_date ,
p_business_group_id => l_business_group_id,
p_last_name => emp_rec.last_name ,
p_first_name => emp_rec.first_name ,
p_title => emp_rec.title, p_sex => emp_rec.sex ,
p_employee_number => emp_rec.employee_number ,
p_date_of_birth => emp_rec.date_of_birth ,
p_person_id => l_person_id ,
p_assignment_id => l_assignment_id ,
p_per_object_version_number => l_per_object_version_number ,
p_asg_object_version_number => l_asg_object_version_number ,
p_per_effective_start_date => l_per_effective_start_date ,
p_per_effective_end_date => l_per_effective_end_date ,
p_full_name => l_full_name ,
p_per_comment_id => l_per_comment_id ,
p_assignment_sequence => l_assignment_sequence ,
p_assignment_number => l_assignment_number ,
p_name_combination_warning => l_name_combination_warning ,
p_assign_payroll_warning => l_assign_payroll_warning ,
p_orig_hire_warning => l_orig_hire_warning);
fnd_file.put_line (fnd_file.LOG, 'Creating New employee:' emp_rec.employee_number); ELSIF lv_rec_exixsts = 'Y' THEN
SELECT papf.person_id ,
papf.object_version_number ,
ppos.actual_termination_date ,
ppos.OBJECT_VERSION_NUMBER ,
ppos.PERIOD_OF_SERVICE_ID INTO l_person_id ,
l_per_object_version_number ,
l_acutal_temination_date_ewgl ,
l_ser_object_version_number ,
l_service_id
FROM per_all_people_f papf, per_periods_of_service ppos
WHERE employee_number = emp_rec.employee_number
AND papf.person_id = ppos.person_id
and papf.business_group_id=l_business_group_id
AND SYSDATE BETWEEN EFFECTIVE_START_dATE AND EFFECTIVE_END_DATE;
l_employee_number := emp_rec.employee_number;
fnd_file.put_line(fnd_file.log,'person_id:'l_person_id);
fnd_file.put_line(fnd_file.log,'Object Version Number:'l_per_object_version_number); fnd_file.put_line(fnd_file.log,'EWGL Termination date:'l_acutal_temination_date_ewgl); fnd_file.put_line(fnd_file.logi,'Last Update date:'emp_rec.last_updaite_date); fnd_file.put_line(fnd_file.log,'HRMS Employee Actual Termination Date:'emp_rec.actual_termination_date); f
nd_file.put_line(fnd_file.log,'Service Object Version:'l_ser_object_version_number);
IF emp_rec.last_update_date>=l_last_run_date
AND l_acutal_temination_date_ewgl IS NULL and emp_rec.actual_termination_date is null THEN
hr_person_api.update_person (p_effective_date => emp_rec.effective_start_date
,p_datetrack_updatie_mode => 'CORRECTION'
,p_person_id => l_person_id
,p_object_version_number => l_per_object_version_number
,p_employee_number => l_employee_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_full_name => l_full_name
,p_last_name => emp_rec.last_name
,p_first_name => emp_rec.first_name
,p_date_of_birth => emp_rec.date_of_birth
,p_comment_id => l_comment_id
,p_name_combination_warning => l_name_combination_warning
,p_assign_payroll_warning => l_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning);
fnd_file.put_line (fnd_file.LOG, 'UPDATING New employee:' emp_rec.employee_number);
END IF;
IF emp_rec.actual_termination_date IS NOT NULL and emp_rec.last_update_date>=l_last_run_date THEN
l_last_std_process_date_out := emp_rec.final_process_date; hr_ex_employee_api.actual_termination_emp
(p_effective_date => emp_rec.effective_start_date
,p_period_of_service_id => l_service_id
,p_object_version_number => l_ser_object_version_number
,p_actual_termination_date => emp_rec.actual_termination_date
,p_person_type_id=>null
,p_last_std_process_date_out => l_last_std_process_date_out
,p_supervisor_warning => l_supervisor_warning
,p_event_warning => l_event_warning
,p_interview_warning => l_interview_warning
,p_review_warning => l_review_warning
,p_recruiter_warninig => l_recruiter_warning
,p_asg_future_changes_warning => l_asg_future_changes_warning
,p_entries_changed_warning => l_entries_changed_warning ,
p_pay_proposal_warning => l_pay_proposal_warning ,
p_dod_warning => l_dod_warning ,
p_alu_change_warning => l_alu_change_warning);
fnd_file.put_line (fnd_file.LOG, 'Terminating New employee:' emp_rec.employee_number);
END IF;
IF l_acutal_temination_date_ewgl IS NOT NULL
AND emp_rec.actual_termination_date IS NULL and emp_rec.last_update_date>=l_last_run_date THEN hr_employee_api.re_hire_ex_employee
(p_hire_date => emp_rec.effective_start_date ,
p_person_id => l_person_id ,
p_per_object_version_number => l_per_object_version_number ,
p_rehire_reason => ' ', p_assignment_id => l_assignment_id ,
p_asg_object_version_number => l_asg_object_version_number ,
p_per_effective_start_date => l_per_effective_start_date ,
p_per_effective_end_date => l_per_effective_end_date ,
p_assignment_sequence => l_assignment_sequence ,
p_assignment_number => l_assignment_number ,
p_assign_payroll_warning => l_assign_payroll_warning);
fnd_file.put_line (fnd_file.LOG, 'RE Hiring New employee:' emp_rec.employee_number); END IF; END IF; END LOOP;
commit;
END;

Videos 2 Create FORMS

2011-08-02T12:26:00.000-04:00

Creating A Form Video-1

Creating A Form Video-2

Creating A Form Video-3

Creating A Form Video-4(Creating LOV)

Links 2 Download Material

2011-08-02T12:08:00.000-04:00

12 Steps for Enabling Flexfields in Oracle Report.doc (0.04MB)
http://www.filefactory.com/file/cdb082a/n/12_Steps_for_Enabling_Flexfields_in_Oracle_Report.doc
Download now!

Account Receivebles Q&A.docx (0.02MB)
http://www.filefactory.com/file/cdb082b/n/Account_Receivebles_Q&A.docx
Download now!

AOL Flexfields.doc (0.05MB)
http://www.filefactory.com/file/c2a25f2/n/AOL_Flexfields.doc
Download now!

FLEX FIELDS.doc (0.05MB)
http://www.filefactory.com/file/cdb082c/n/FLEX_FIELDS.doc
Download now!

FLEX_FIELD1.doc (0.27MB)
http://www.filefactory.com/file/cdb082d/n/FLEX_FIELD1.doc
Download now!

FND FLEXSQL.doc (0.04MB)
http://www.filefactory.com/file/cdb082e/n/FND_FLEXSQL.doc
Download now!

Form Personalization.doc (0.58MB)
http://www.filefactory.com/file/cdb0820/n/Form_Personalization.doc
Download now!

Oracle Apps 11ifilessystem.doc (0.21MB)
http://www.filefactory.com/file/cdb0822/n/Oracle_Apps_11ifilessystem.doc
Download now!

Concurrent Report

2011-08-01T14:28:00.000-04:00

Report Parmaeters Default type Value sets Report standards

1)How will you develop the Report?
8 steps

2)What is Custom Top?

3)What is the diff between APPS Schema and PO Schema?

4) Can we create Table in Apps Schema?
Yes But we are not suppose to create

5)What is concurrent Program?
Executable Parameters , Incompatible Programs

6)What is Request group?
Collection of Concurent Programs and Reports

7)how to schedule concurrent Program?
At SRS window by selecting Schedule button

8)What are the Standards we will follow for Oracle reports in Application?
1)P_CONC_REQUEST_ID
2)srw.user_exit('FND SRWINIT');
3)srw.user_exit('FND SRWEXIT');

9)What is User Exit?
Program which will transfer the control from Report builder to some other 3GL

10)Types of user Exists? 5

FND SRWINIT
FND SRWEXIT
FND FORMATCURRENCY
FND FLEXSQL
FND FLWXIDVAL

11)what is Token?
Maping between Concurrent Program Parameter and Report Bind Variable

12)What is the Mandatory parameter in Reports?
P_CONC_REQUEST_ID

13) Can we define parameter without Token?
No

14)What are the Default types we have?
SEGMENT CONSTANCE SQL Statement Profile

15)What is Range option?
Will be used to maintain the Condition Between Two parameters like Low and High

16)What is value set?
List of values with Validations

17)What are the different value sets we have?
8

18)What is Table value set?
Displaying the values from db Table column

19)Can we delete value set?
Yes, we can delete IF Value set is not used by any concurrent Program in application

19)Can we delete Concurrent Program?
No, we can disable by Unchecking Enabled Check Box

20)What are the things we need to add for responsibility?
Menu , Request Group , Data Group

21)What is SRS Window?
Will be used to submit Request and To schedule Requests.

22)How to get Request ID into the report Output?
By using P_CONC_REQUEST_ID Bind Variable

23)Can we delete Concurrent Parameter?
Yes, we can delete

24)Can we delete User name from the Application?
NO, We can disable by giving End date

25)While creating Executable -->we will give the Application Name
While creating Concurrent programe--> we will give the Application Name what is the diff between these two?

Ans)Executable Application is nothing but Where Exactly .rdf File is Located
Concurrent Program Application Name is nothing but From which application we can submit Program
we can define in Multple Application if we required

26)Can we create Two concurrent Programs with same short name and application name in Apps?
NO

GE OUT BOUND CODING

2011-08-01T14:26:00.000-04:00

CREATE OR REPLACE procedure GE_INV_Out1(Errbuf OUT varchar2,
Retcode ouT varchar2,
f_id in number,
t_id in varchar2) as
cursor c1 is select
msi.segment1 item,
msi.inventory_item_id Itemid,
msi.description itemdesc,
msi.primary_uom_code Uom,
ood.organization_name name,
ood.organization_id id,
mc . segment1||','||mc.segment2 Category
from
mtl_system_items_b msi,
org_organization_definitions ood,
mtl_item_categories mic,
mtl_categories mc
where
msi.organization_id = ood.organization_id
and msi.inventory_item_id = mic.inventory_item_id
and msi.organization_id = mic.organization_id
and mic.category_id = mc.category_id
and msi.purchasing_item_flag = 'Y'
and msi.organization_id between f_id and t_id;
x_id utl_file.file_type;
l_count number(5) default 0;
begin
x_id:=utl_file.fopen('d:\oracle\proddb\8.1.7\plsql\temp','invoutdata.dat','W');
--select * from v$parameter where name like '%utl_file%'
for x1 in c1 loop
l_count:=l_count+1;
utl_file.put_line(x_id,x1.item ||'-'||
x1.itemid ||'-'||
x1.itemdesc||'-'||
x1.uom ||'-'||
x1.name ||'-'||
x1.id ||'-'||
x1.category );
end loop;
utl_file.fclose(x_id);
Fnd_file.Put_line(Fnd_file.output,'No of Records transfered to the data file :'||l_count);
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted User name '||Fnd_Profile.Value('USERNAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted Responsibility name '||Fnd_profile.value('RESP_NAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submission Date :'|| SYSDATE);
Exception
WHEN utl_file.invalid_operation THEN
fnd_file.put_line(fnd_File.log,'invalid operation');
utl_file.fclose_all;
WHEN utl_file.invalid_path THEN
fnd_file.put_line(fnd_File.log,'invalid path');
utl_file.fclose_all;
WHEN utl_file.invalid_mode THEN
fnd_file.put_line(fnd_File.log,'invalid mode');
utl_file.fclose_all;
WHEN utl_file.invalid_filehandle THEN
fnd_file.put_line(fnd_File.log,'invalid filehandle');

utl_file.fclose_all;
WHEN utl_file.read_error THEN
fnd_file.put_line(fnd_File.log,'read error');
utl_file.fclose_all;
WHEN utl_file.internal_error THEN
fnd_file.put_line(fnd_File.log,'internal error');
utl_file.fclose_all;
WHEN OTHERS THEN
fnd_file.put_line(fnd_File.log,'other error');
utl_file.fclose_all;
End GE_INV_Out1;

PROCEDURE FOR PO & PROCEDURE STEPS

2011-08-01T14:23:00.001-04:00

CREATE OR REPLACE PROCEDURE XX_PO_INTERFACE11
(Errbuf OUT VARCHAR2,
Retcode OUT VARCHAR2) AS
CURSOR c1 IS SELECT * FROM XX_PO_HEADERS_STAGE;
CURSOR c2 IS SELECT * FROM XX_PO_LINES_STAGE;

l_vendor_id number(10);
l_item varchar2(150);
l_flag varchar2(4) default 'A';
l_msg varchar2(200);
l_site_code varchar2(100);
l_curr_code varchar2(10);
l_org_id number(6);

BEGIN
FOR x1 IN c1 LOOP
BEGIN
SELECT vendor_id
INTO l_vendor_id
FROM po_vendors
WHERE vendor_name = x1.VENDOR_NAME;
-- AND ORG_ID = Fnd_Profile.Value('ORG_ID');
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_vendor_id := 0;
l_msg := 'Vendor id is Not in SYSTEM';
END;
--Vendor Site code Validation
/* begin
select vendor_site_code
into l_site_code
from po_vendor_sites_all
where vendor_site_code = x1.vendor_site_code;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_site_code := 0;
l_msg := 'Vendor Site Code is Not in SYSTEM';
END;*/
--End of Site Code Validation
--Currency Code Validation
Begin
select currency_code
into l_curr_code
from fnd_currencies
where currency_code = x1.CURRENCY_CODE;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_curr_code := 0;
l_msg := 'Currency Code is Invalid';
END;
--End of te Currency Validation
--Operating Unit ID Validation
Begin
select organization_id
into l_org_id
from hr_operating_units
where organization_id = x1.org_id;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_org_id := 0;
l_msg := 'Invalid Organization ID';
END;
--End of the ORG ID Validation
IF l_flag != 'E' THEN
INSERT INTO po_headers_interface
(
INTERFACE_HEADER_ID
,BATCH_ID
,ACTION
,ORG_ID
,DOCUMENT_TYPE_CODE
,CURRENCY_CODE
,AGENT_NAME
,VENDOR_NAME
,VENDOR_SITE_CODE
,SHIP_TO_LOCATION
,BILL_TO_LOCATION
,creation_date
,APPROVAL_STATUS
,APPROVED_DATE
,FREIGHT_TERMS
)
VALUES
(
x1.INTERFACE_HEADER_ID
,x1.batch_id
,x1.action
,x1.org_id
,x1.document_type_code
,x1.CURRENCY_CODE
,x1.AGENT_NAME
,x1.VENDOR_NAME
,x1.VENDOR_SITE_CODE
,x1.SHIP_TO_LOCATION
,x1.BILL_TO_LOCATION
,SYSDATE-10
,x1.APPROVAL_STATUS
,SYSDATE
,x1.FREIGHT_TERMS
);
end if;
END LOOP;
FOR x2 IN c2 LOOP
l_flag := 'A';
--Item Validation
begin
select segment1
into l_item
from mtl_system_items_b
where segment1 = x2.item
AND ORGANIZATION_ID = fnd_profile.value('ORG_ID');
exception
when others then
l_flag := 'E';
l_vendor_id := 0;
l_msg := 'Item is not valid Item';
END;
--End of the Item Validation
if l_flag != 'E' then
INSERT INTO PO_LINES_INTERFACE
(
INTERFACE_LINE_ID
,INTERFACE_HEADER_ID
,LINE_NUM
,SHIPMENT_NUM
,LINE_TYPE
,ITEM
,ITEM_DESCRIPTION
,item_id
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,SHIP_TO_ORGANIZATION_CODE
,SHIP_TO_LOCATION
,NEED_BY_DATE
,PROMISED_DATE
,list_price_per_unit
)
VALUES
(
x2.INTERFACE_LINE_ID
,x2.INTERFACE_HEADER_ID
,x2.LINE_NUM
,x2.SHIPMENT_NUM
,x2.LINE_TYPE
,x2.ITEM
,x2.ITEM_DESCRIPTION
,x2.item_id
,x2.UOM_CODE
,x2.QUANTITY,
X2.UNIT_PRICE,
X2.SHIP_TO_ORGANIZATION_CODE,
X2.SHIP_TO_LOCATION,
X2.NEED_BY_DATE,
X2.PROMISED_DATE,
X2.LIST_PRICE_PER_UNIT);
END IF;
END LOOP;
COMMIT;
END XX_PO_INTERFACE11;
/

1)Create the Staging tables
2)Develop the Control files and register as concurrent program
3)Develop the PL/SQL Program and write the validations and insert into interface table
4)run the standard program called Import Standard Purchase orders from PO Reponsibility
Parameter : Default Buyer :null
Create or update items :No
PO Status :APPROVED
Batch ID :13 (We can get from headers interface table)
5)Take the Request ID execute following query we can get the PO numbers

select segment1 POnumber
from po_headers_all
where request _id = 145233;
6)Go to the PO Application and Query the PO from as per the PO number.

GL_PROCEDURE CODING & PROCESS STEPS

2011-08-01T14:20:00.001-04:00

CREATE OR REPLACE PROCEDURE GL_PAT123(Errbuf OUT VARCHAR2,
Retcode OUT VARCHAR2) IS
-- cursor declaration
CURSOR gl_cur IS
SELECT
status ,
set_of_books_name ,
accounting_date ,
currency ,
date_created ,
created_by ,
actual_flag ,
source ,
category ,
segment1 ,
segment2 ,
segment3 ,
segment4 ,
segment5 ,
xx_entered_dr ,
xx_entered_cr
FROM GLINT ;
l_currencycode VARCHAR2(25);
l_set_of_books_id NUMBER(3);
l_flag VARCHAR2(2);
l_error_msg VARCHAR2(100);
l_err_flag VARCHAR2(10);
l_category VARCHAR2(100);
L_USERID NUMBER(10);
BEGIN
DELETE FROM gl_interface;
COMMIT;
FOR rec_cur IN gl_cur LOOP
l_flag:='A';
l_err_flag:='A';
--This PL/SQL Block will do the currency validation
--end of the currency validation
--Category Column Validation
BEGIN
SELECT USER_JE_CATEGORY_NAME
INTO l_CATEGORY
FROM GL_JE_CATEGORIES
WHERE USER_JE_CATEGORY_NAME = REC_CUR.Category;
EXCEPTION
WHEN OTHERS THEN
l_category:=NULL;
l_flag:='E';
l_error_msg:='Category does not exist ';
END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
--End Category Column Validation
--User ID column validation
BEGIN
SELECT USER_ID
INTO L_USERID
FROM FND_USER
WHERE USER_ID = REC_CUR.created_by;
EXCEPTION
WHEN OTHERS THEN
L_userid:=NULL;
l_flag:='E';
l_error_msg:='User ID does not exist ';
END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
--End of Created_by OR UserID column Validation
--Set of books Validation
BEGIN
SELECT set_of_books_id
INTO l_set_of_books_id
FROM GL_SETS_OF_BOOKS
WHERE set_of_books_id=rec_cur.set_of_books_id;
EXCEPTION
WHEN OTHERS THEN
l_set_of_books_id:=NULL;
l_flag:='E';
l_error_msg:='set of Books ID does not exist ';
END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
-- End Set of books Validation
--Status Column validation
/* BEGIN
IF rec_cur.status = 'NEW' THEN
l_flag:= 'A';
ELSE
l_flag:= 'E';
Fnd_File.put_line (Fnd_File.LOG,'Status column has got invalid data');
END IF;
END;*/
BEGIN
SELECT currency_code
INTO l_currencycode
FROM fnd_currencies
WHERE currency_code=rec_cur.currency
AND currency_code='USD';
EXCEPTION
WHEN OTHERS THEN
l_currencycode:=NULL;
l_flag:='E';
l_error_msg:='currency code does not exists';
END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||L_FLAG);
--End of Actual Flag Column validation
IF l_flag!='E' THEN
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE');
INSERT INTO gl_interface(
status,
set_of_books_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_source_name,
user_je_category_name ,
segment1,
segment2,
segment3,
segment4,
segment5,
entered_dr,
entered_cr,
)
VALUES
(rec_cur.status ,
rec_cur.set_of_books_name ,
rec_cur.accounting_date ,
rec_cur.currency ,
rec_cur.date_created ,
rec_cur.created_by ,
rec_cur.actual_flag ,
rec_cur.source ,
rec_cur.category ,
rec_cur.segment1 ,
rec_cur.segment2 ,
rec_cur.segment3 ,
rec_cur.segment4 ,
rec_cur.segment5 ,
rec_cur.xx_entered_dr ,
rec_cur.xx_entered_cr ,
);
END IF;
l_flag:=NULL;
l_error_msg:=NULL;
END LOOP;
COMMIT;
END GL_PAT123;

1)we have received flat file from client

2)We have created Staging table as per flat file structre

3)developed Control file and uploaded data

4)Developed PL/SQL Program to upload the data from stage into interface table
1)declare Cursor
2)open cursor
3)Validate each record
4)If no invalid record then insert into interface table.

5)Run the journal import from GL => Journal => Import => Run
Give the two parameters 1)Source
2)Group ID

6) Open the Output if status is 'SUCEESS' then take Request ID.

7)open Journal Enter screen Query the records based on the %requestid% As batch Name
Select Review journal button we can see the journal detailed transaction

8) If we want correct the journals we can correct from Journal=>Import=>Correct

9) If we want delete the journals we can delete from Journal=>Import=>Delete

Execution Method

2011-08-01T14:15:00.001-04:00

• Execution Method - Here you need to choose the appropriate execution method for your executable. Following are the possible execution methods which are commonly used in oracle applications.

• Oracle Reports – used for the RDF reports

• Host – used for shell scripts, basically the language of the host operating system

• PL/SQL Stored procedure – used to run the stored procedure through oracle applications

• SQL*Loader – used to run the sql loader programs

• SQL*Plus - used to run the anonymous PL/SQL blocks. It will get executed in the same fashion as you are running on SQL Plus.

• Java Stored Procedure – The execution file is a Java stored procedure.

• Java Concurrent Program – Used for program written in Java.

• Spawned – used for c or pro*c Program. Mainly used by standard oracle interfaces.

• Perl Concurrent Program – used for programs written in CGI Perl.

• Request Set Stage Function – PL/SQL stored function that can be used to calculate the completion statuses of request set stages.

• Immediate – execution file is a program written to run as subroutine of the concurrent manager. Oracle doesn’t recommend use of this executable type.

• Multi-Language function – execution file is an MLS function that supports running concurrent program in multiple languages.

• PL/SQL Stored Procedure, Oracle Reports, Host, SQL*loader, SQL*Plus are the most commonly used executable types.

• Executable File name – This should contain the name of the executable file. In case of PL/SQL Stored procedure or Java Stored Procedure it should be the fully defined name of the stored procedure.

• Subroutine Name – This field is only used when executable type is spawned or immediate.

• Execution File Path –

• The Stage Function Parameters button opens a window that allows you to enter parameters for the Request Set Stage Function. This button is only enabled when you select Request Set Stage Function as your Execution Method.

Concurrent Program

• Once you have defined the executable, the second step is to define the concurrent program. To define a concurrent program, you need to open the form using the navigation path below.

• Navigation –

– Application Developer

– Concurrent

– Program

• Field Description

• Program – Give user friendly name for your concurrent program. This name will be displayed in Requests submission screen while submitting the requests.

• Short Name – Give short name for concurrent program. This is used within the database tables in oracle applications. It’s a common practice to have the Short name same for the executable and the concurrent program.

• Application – Give the application to which the concurrent program belongs to.

E.g you want some program to run from oracle payables then account payables should be entered as application name.

• Description - Give the brief description of the concurrent program.

• Executable - Enter the short name of the executable (Defined in Executables Screen) you want to attach to this concurrent program.

• Method - will be defaulted once you enter executable short name

• Options - will be defaulted once you enter executable short name

• Normally default values are given for the other fields. Following is the significance of these fields.

• Request Type – Concurrent program can be associated to a predefined request type so that only few concurrent managers can run the program.

• Incrementor – To be used by Oracle only.

• MLS Function - This feature allows the program to be submitted once by the user but runs it in the multiple languages.

• Use In SRS – Only when this check box is checked the Concurrent Program would be available in Standard Request Submission (SRS) through the request group.

• Allow Disable Value – This will allow the disabled values in the value sets to be used while entering the values of the parameters in the Concurrent Program.

• Run Alone – Indicates that program is incompatible with all other concurrent programs and should be run alone.

• Enable Trace – This will enable the SQL trace for the concurrent program and will generate the trace file when concurrent program is run. Only used in development environments to check the performance of the concurrent program.

• Restart on system Failure – This option is used to indicate that concurrent program should automatically be started when concurrent manager is restored after the system failure.

• NLS Compliant – This box is checked if the program allows for a user to submit request of the program that will reflect a language and territory that are different from the language and territory that the users are operating in.

• Output Format – Format in which output should be printed. Possible format values are

• HTML

• PDF

• TEXT

• PS (Post Script)

• PCL(HP’s Printer Control Language)

• SAVE – Check to indicate that output should be automatically saved in an operating system file.

• PRINT – Whether you want the output to be sent to printer for printing.

• Column / Rows – Column and Row length of the concurrent program output. Oracle Applications uses this information to decide the print style.

• Style Required – Print Style

• Printer – A particular printer on which output should be sent.

• Save the data that you have entered using Ctrl+S or File à Save.

• Program – will be defaulted from Concurrent Program

• Application – will be defaulted from Concurrent Program

• Conflicts Domain - Enter the parameter which will hold the value of the conflict domain of the program.

• Security Group - This field is for HRMS security only.

• Seq – Enter sequence number for the parameter

• Parameter – name of the parameter, will be displayed in parameter entry screen

• Description – description about the usage of the parameter

• Enabled – check box to enable or disable the parameter. Disable a parameter when you don’t want to use it.

• Value set – enter the name of the value set which you want to use to validate the value enter in the parameter field.

• Description – will be defaulted from value set definition

• Default Type – choose the default type for the default value of the parameter. Possible default types are

• Constant : The default value can be any literal value.

• Profile : The default value is the current value in the user profile option defined in the Default Value field. Use the profile option name, not the end–user name. You do not need to include $PROFILE$.

• SQL Statement : The default value is determined by the SQL statement you defined in the Default Value field.

• Segment : The default value is the value entered in a prior segment of the same parameter window.

• Required

If the program executable file requires an argument, you should require it for your concurrent program.

• Enable Security

If the value set for this parameter does not allow security rules, then this field is display only. Otherwise you can elect to apply any security rules defined for this value set to affect your parameter list.

• Display size – Enter the field length in characters for this parameter. The user sees and fills in the field in the Parameters window of the Submit Requests window.

• Token - For a parameter in an Oracle Reports program, the keyword or parameter appears here. The value is case insensitive. For other types of programs, you can skip this field.

Incompatible Programs Window

• To navigate to this form, click on the Incompatibilities button on the bottom of the form shown in figure 4. This window is used for defining the incompatibilities of the concurrent program. Identify concurrent programs that should not run with your concurrent program as they might interfere with its execution.

• Program – Defaulted from Concurrent Programs Window

• Application – Defaulted from Concurrent Programs Window

• Application – Application of the concurrent program which is incompatible to the defined concurrent program.

• Name – Name of the concurrent program which is incompatible to the defined concurrent program.

• Scope – Used to identify if concurrent program is incompatible if the program or also with its child requests.

Type - Enter Domain or Global. If you choose Domain, the incompatibility is resolved at a domain-specific level. If you choose Global, then this concurrent program will be considered globally incompatible with your concurrent program, regardless of which domain it is running in.

Session Control

• To navigate to this form, click on the Session Control button on the bottom of the form shown in figure 4. This is a new functionality given in 11.5.10 to control the database session while executing concurrent program using following three parameters.

• Consumer Group – resource consumer group of the concurrent program can be specified. A resource consumer group defines a set of users who have similar resource usage requirements. An overall resource plan specifies how resources are distributed among the different resource consumer groups. Resource consumer groups and resource plans provide a method for specifying how to partition processing resources among different users.

• Rollback Segment – Rollback segment specified here would be used instead of the default rollback segment. If you specify a rollback segment here, your concurrent program must use the APIs FND_CONCURRENT.AF_COMMIT and FND_CONCURRENT.AF_ROLLBACK to use the specified rollback segment.

• Optimizer mode - Optionally specify an optimizer mode. You can choose ALL_ROWS, FIRST_ROWS, Rules, or Choose. You would specify an optimizer mode only for a custom program that may not perform well with the default cost-based optimizer (CBO) and needs tuning. You can use a different optimizer mode until your program is tuned for CBO.

Copy to Window

• Program – Enter the program name to be copied

• Short Name – Short Name of the program to be copied

• Application – Application name of the concurrent program to be copied

• Include Incompatible programs - Check this box if you want incompatibilities to be copied

• Include Parameters – Check this box if you want parameters to be copied.

Request Not Yet Completed

2011-08-01T14:05:00.000-04:00

Canceling or Holding a Request That Has Not Yet Completed

1. Navigate to the Requests window.

2. Select the Specific Request option button and enter the Request ID or other appropriate search criteria or select another appropriate option.

3. Click Find.

4. With the cursor on the request you want to cancel, click Cancel request. If the request is in Pending or Inactive phase, click Cancel Request or Hold Request to either cancel the request or put the request on hold. If the request is in Running phase, click Cancel Request to terminate the request.

If you cancel a request set, then Oracle Applications will automatically cancel all requests in the set.

5. Choose Save from the File menu.

Changing Request Options

If your request or request set has not started running, you can change how your request or request set runs and prints its output by using the Requests window

How to Change the Request Options of a Request or Request Set

1. Navigate to the Requests window.

2. Use the Find window to search for your request or request set ID.

3. Click Hold Request to put your request on hold.

4. You can change the start date and time for your request or request set. If the request is Pending or Scheduled, you can change the printer, the number of copies you want to print, and whether Oracle Application saves your report output to an operating system file. You can also change resubmission options in this window.

Practice Solution

• Choose the Payables, Vision Operations responsibility link on the Personal Homepage, or (M) File > Switch Responsibility and choose it from the list.

• To get to the Requests window, (N) Other > Requests > Run.

• In the Submit a New Request window, select the option Single Request.

• When the Requests window appears, use the list of values to enter Accounts Payable Trial Balance Report in the Request Name field.

• Click the Submit button and your request goes to the queue to run.

• (N) Other > Requests > View, select All My Requests and click Find.

• Click the Refresh Data button to view the progress of your report until it completes.

• Click the View Output button to see the report on your screen.

Phase and Status for a Concurrent Request

2011-08-01T14:03:00.000-04:00

Phase and Status for a Concurrent Request

The Concurrent Requests Summary window displays a phase and status summary for each concurrent request listed in the window.

A concurrent request has a life cycle consisting of the following phases:

•Pending: The request is waiting to be run.

•Running: The request is running.

•Completed: The request has finished execution.

•Inactive: The request cannot yet be run.

During each phase, a concurrent request has a specific condition or status. The table lists each phase/status combination and describes its meaning in relation to a request.

Pending Phase

A program in the Pending phase can be in one of four statuses:

•Normal: The program is waiting for an available manager.

•Waiting: A child program is waiting for a parent to mark it ready to run.

•Standby: A program is waiting for another incompatible program in the same logical database to complete.

•Scheduled: A program’s scheduled start time has not yet elapsed.

Running Phase

A program in the Running phase can be in one of four statuses:

•Normal: Program is in progress.

•Paused: A parent program is waiting for one or more child programs to complete.

•Resuming: A parent program is continuing after the completion of one or more child programs.

•Terminating: The program is being terminated.

Completed Phase

A program in the Completed phase can be in one of five statuses:

•Normal: Program completed successfully.

•Warning: Program completed successfully but with warning messages.

•Error: Program failed to complete successfully.

•Terminated: A running program was terminated.

•Canceled: A pending or inactive program was canceled before it started.

Inactive Phase

A program in the Inactive phase can be in one of three statuses:

•Disabled: The requested program has not been enabled for execution.

•On Hold: The requested program has been placed on hold.

•No Manager: There is no manager defined to run this type of request.

Using Request Set

2011-08-01T13:53:00.000-04:00

Using a Request Set

After you define a request set, it becomes your private request set. You can run it as long as you have access to a standard Submit Requests window that does not limit access to specific requests.

Other users can run the request set only if your system administrator assigns the request set to their responsibility’s request group. It is possible to have a request set in your request group that contains individual requests that are not in your request group.

You can only edit request sets that you own. You can add any requests in your request group to the request set. You can delete any request from the request set, regardless of whether that request is in your request group. To update information about an individual request in the request set, however, the request set must be in your request group.

Viewing and Changing Requests

Since all reports, programs, and request sets are run as concurrent requests in Oracle Applications, you can navigate to the Request window to view the progress and output of all your concurrent requests, and you can change aspects of a request’s processing options.

You can use the Request window (summary and detail) to view a list of all the submitted concurrent requests, check whether your request has run, change aspects of a request's processing options, diagnose errors, or find the position of your request in the queues of available concurrent managers.

How to Use the Request Window

1. Upon initial navigation to the Requests window, you are directed to the Find Requests window.

2. Use the Find Requests window to specify the types of requests you want to see. You can choose to view your completed requests, your requests in progress, or all of your requests by selecting the appropriate option group.

In addition, you can look for a particular request using the Specific Requests option group. Use the find criteria fields as you would for any other find window to specify as many or as few details as you need to locate the desired request information. Navigate to the Find Request window.

3. Enter specific criteria in the Find window that appears, or click Find to display all of your requests that you have submitted.

4. Use the Order By poplist to specify the order in which you want your requests displayed.

Performing Concurrent Processing Tasks

You can click various buttons in this window to perform tasks relating to concurrent processing.

•Refresh Data button: The Requests window will not automatically refresh in order to display updated progress of your request. Use this button to requery the lines in the request table.

•Find Requests button: Displays the Find Request window to perform a search for additional requests

•Submit a New Request button: Displays the Submit Requests window to submit a new request to the concurrent manager

•Hold Request and Cancel Request buttons: These buttons illuminate if the concurrent manager has not already begun running the program. You can Hold or Cancel a request by using these buttons. If the request is already running the Cancel button will terminate the request.

•View Details button: Displays the Details window to view detailed information about your request, for example, submission dates, scheduling and other information. If a request has not already run, you can change selected fields so that the updated information will affect your request when it is run by the concurrent manager.

•Diagnostics button: Displays diagnostic information about your request such as when it ran and if it completed successfully.

•View Output button: Displays an online format of your report.

Note: Reports that produce an output other than a text file (for example HTML or PDF) are displayed in a browser. If you set the profile option VIEWER: TEXT to Browser, your text files are also displayed in a browser as opposed to the Report Viewer. VIEWER: TEXT may also be set to display your output in a text editor such as Microsoft Word, or in a spreadsheet such as Microsoft Excel.

•View Log button: Displays information about the request regarding arguments used and other technical information

•Screen colors help you identify the status of the request.

–Green: Pending, running or scheduled requests

–Yellow: Completed requests with a status of Warning

–Red: Requests that complete with a status of Error.

Phase and Status for a Concurrent Request

The Concurrent Requests Summary window displays a phase and status summary for each concurrent request listed in the window.

A concurrent request has a life cycle consisting of the following phases:

•Pending: The request is waiting to be run.

•Running: The request is running.

•Completed: The request has finished execution.

•Inactive: The request cannot yet be run.

During each phase, a concurrent request has a specific condition or status. The table lists each phase/status combination and describes its meaning in relation to a request.

Pending Phase

A program in the Pending phase can be in one of four statuses:

•Normal: The program is waiting for an available manager.

•Waiting: A child program is waiting for a parent to mark it ready to run.

•Standby: A program is waiting for another incompatible program in the same logical database to complete.

•Scheduled: A program’s scheduled start time has not yet elapsed.

Organizing Requests with Stages

2011-08-01T13:39:00.000-04:00

Organizing Requests with Stages

Request sets are divided into one or more stages which are linked to determine the sequence in which your requests are run. Each stage consists of one or more requests that you want to run in parallel (at the same time in any order). For example, in the simplest request set structure, all requests are assigned to one stage. This allows all requests to run in parallel.

To run requests in sequence, you assign requests to different stages, and then link the stages in the order you want the requests to run.

The concurrent manager allows only one stage in a request to run at a time. When one stage is complete, the next stage is submitted. A stage is not considered complete until all of the requests in the stage are complete. One advantage of using stages is the ability to run several requests in parallel and then move sequentially to the next stage. This enables you to create more versatile and efficient request sets.

Defining Request Sets

By defining request sets, you can submit the same set of requests repeatedly using a single transaction. Use the Request Set window to create and edit request sets.

1. Navigate to the Request Set window.

2. Enter a Name for your request set.

3. Enter a Short Name for your request set. This name is used internally to reference your request set.

4. Enter the Application with which you want to associate your request set.

5. Enter a Description of your request set if you like.

The Owner field defaults to your username and can only be changed by your system administrator.

6. Enter the Active Dates From and To fields to define an effective period when you and others can run the request set. If the current date is outside the range you define, the request set will not be available in the Submit Requests window.

7. Check the Print Together check box to send all your requests to the printer together when they complete, or uncheck the check box to send each request one at a time to the printer as it completes.

8. Check the Allow Incompatibility check box to allow your system administrator to specify programs that this request is incompatible with (may not run with). Leave Allow Incompatibility unchecked to specify that this request set may run with all other concurrent requests or request sets.

9. Choose Define Stages or Link Stages if you have finished defining your stages. See Defining Stages or Linking Stages.

Defining Stages

The value for the Display Sequence is defaulted in sequence as you enter your stages. You may change the display order of the stages by modifying the field.

1. Enter a name for the stage.

2. Enter a Description for your stage.

3. Enter a Short Code for the stage. This code is used internally to reference the stage.

4. Use the LOV in the Function field to select a function. The default value for this field is the Standard Stage Evaluation function. This function bases its completion status on the normal completion status of the requests it contains.

5. Use the Return Value of this Stage Affects the Set Outcome check box if you want to ensure that the request set’s completion status is equal to the completion status of this stage.

Note: If you select this box for more than one stage, the completion status of the request set will equal the completion status of the last of these stages to run in the set.

6. Use the Allow Incompatibility box as described above, to determine if this stage is incompatible with other concurrent processing programs.

7. Click Requests to display the Stage Requests window.

Adding Requests to a Stage

In this window you define which requests you want to include in this stage.

1. Select the report or program you want to include in your request set. Note that the description of the request you choose and its associated application appears in the Description and Application fields.

2. Specify the number of Copies of output to print, the Style to print, the Printer to print to, and whether to save the output to an operating system file.

3. Use the Allow Stage Function to Use This Program’s Result’s check box to indicate which programs or reports should be included.

4. Click Parameters to display the Request Parameters window.

Request Parameters Window

The Request Parameters window lets you customize the parameter values of a specific request in a request set. The fields at the top of the Request Parameters window list general information about the current request set and the request for which you can customize the parameter values.

The multi-row portion of the window lists the parameters for that request.

How to Define Parameters for the Requests

1. The Sequence field displays the order in which each request parameter appears when you run the request in the Submit Requests window (lower numbers appear before higher numbers). Only your system administrator can change a parameter's order.

The Prompt field is a display-only field that shows the request parameter's prompt.

2. Check the Display check box to specify that you can see a request parameter at submission time, or uncheck the check box to specify that a parameter should not be displayed at submission time.

3. Check the Modify check box to specify that you can insert or change the value for a request parameter at submission time, or uncheck the check box to specify that a parameter cannot be changed at submission time.

4. Use the Shared Parameter field to set a default value for a parameter that occurs in more than one report or program of a request set. Once you enter the same parameter label in the Shared Parameter field for each occurrence of the same parameter, the value that you assign to the first occurrence of the parameter becomes the default value for all subsequent occurrences of the parameter. The shared parameter label simply enables you to set an initial default value for all occurrences of the same parameter so you can avoid typing the same value all over again for every occurrence of the parameter.

5. Optionally enter a Default Type and Value for the parameter.

6. Save your work.

7. Go back to the Stage Requests window to add more requests to the request set stage.

8. You can select a request more than once if you want to run the same request with different default parameter values.

9. To start a new stage, return to the Stage window and choose New Record from the File Menu.

Linking Stages

Now that you have defined your stage or stages, you will need to link them in the order that you want them to run.

How to Link the Stages

1. Navigate to the Requests Sets window.

2. Enter the Start Stage. The stage you enter here is the first stage submitted for the request set.

3. Enter the stages you want to run following the first stage in the Success, Warning, and Error columns. To ensure that a particular stage follows the preceding stage regardless of the completion status, enter the desired stage in all three columns. To stop the request set if a stage ends in Error, leave the Error column blank. Any time you do not specifically indicate which stage should follow for a completion status, the request set will exit on that completion status. In the following example, the request set will always exit if any stage returns a completion status of error. In addition, stages C and D will terminate the request set regardless of their completion status. If Stage A returns a status other than Error, Stage B will be submitted. Finally, when Stage B completes with a status of Success, it is followed by Stage C, or if the status is Warning, Stage D will follow.