ORACLE STUDENT STUFF: 06/06/12

Wednesday 6 June 2012

BackEnd Registration for All

For Registration of Reports,Pl/SQL Procedures,SQL * Plus... etc from Back end By using PL/SQL Procedure And code shown below.

For Executables:-
==========
fnd_program.executable (
executable => 'ZENGLJEINT'
,short_name => 'ZENGLJEINT'
,application => 'EAG Customization'
,description => 'ZENSI GL Journal Entry Interface (Loader and Validation)'
,execution_method => 'Host'
,execution_file_name => 'ZENGLJEINT'
,subroutine_name => NULL
,ICON_NAME => NULL
,LANGUAGE_CODE => 'US'
,EXECUTION_FILE_PATH => NULL
);

For Concurent Programs:
================
fnd_program.register (
program => 'ZENSI GL Journal Entry Interface (Loader and Validation)'
,application => 'Oracle General Ledger'
,enabled => 'Y'
,short_name => 'ZENGLJEINT'
,description => 'ZENSI GL Journal Entry Interface (Loader and Validation)'
,executable_short_name => 'ZENGLJEINT'
,executable_application => 'Oracle General Ledger'
,priority => NULL
,save_output => 'Y'
,print => 'N'
,cols => 132
,rows => 45
,style => 'Landscape'
,style_required => 'N'
,printer => ''
,use_in_SRS => 'Y'
,allow_disabled_values => 'N'
,run_alone => 'N'
,enable_trace => 'N'
,restart => 'Y'
,nls_compliant => 'Y'
,output_type => 'Text'
,execution_options => NULL
,request_type => NULL
,request_type_application => NULL
,icon_name => NULL
,language_code => 'US'
,mls_function_short_name => NULL
,mls_function_application => NULL
,incrementor => NULL
);

For Concurrent Program Parameters:
=======================
fnd_program.parameter (
program_short_name => 'ZENGLJEINT'
,application => 'Oracle General Ledger'
,sequence => 1
,parameter => 'Base Path'
,description => 'Base Path'
,enabled => 'Y'
,value_set => 'FND_CHAR240'
,default_type => 'Constant'
,default_value => v_base_path
,required => 'Y'
,enable_security => 'N'
,range => ''
,display => 'Y'
,display_size => 15
,description_size => 50
,concatenated_description_size => 25
,prompt => 'Base Path'
,token => ''
);

For Request Groups:
==============
fnd_program.request_group
fnd_program.add_to_group - Add the C.P to the Request Group

FlexSQL,FlexID Code in Reports

QUERY:-

SELECT AI.INVOICE_NUM,
AID.DISTRIBUTION_LINE_NUMBER,
&SELECT_ALL FLEX_DATA
FROM AP_INVOICES_ALL AI,
AP_INVOICE_DISTRIBUTIONS_ALL AID,
GL_CODE_COMBINATIONS GCC
WHERE AI.INVOICE_ID=AID.INVOICE_ID
AND GCC.CODE_COMBINATION_ID=AID.DIST_CODE_COMBINATION_ID
AND AI.ORG_ID=:P_ORG_ID

WE HAD WRITTEN ONE LEXICAL PARAMETER AS &SELECT_ALL (IT IS NOTHING BUT PLACE HOLDER COLUMN(REPORT LEVEL) AND IT CONTAINS SEGMENTS STRUCTURE AND DATATYPE AS CHAR AND INITIAL VALUE AS -->VALUE IF NULL-->GCC.SEGMENT1) ).

NOW PLACE TWO FORMULA COLUMNS .... ONE FOR FLEX DATA AND ONE FOR FLEX DESC WHICH SHUD BE IN GROUP LEVEL BY USING FLEXIDVAL USEREXITS....

NOW WE WANT TO DISPLAY STRUCTURE BY USING FNDFLEXSQL ... FOR DISPLAYING THE STRUCTURE WE SHUD WRITE CODE IN " BEFORE REPORT TRIGGER" WHICH IS SHOWN BELOW..................

BEFORE THIS WE SHUD CREATE TWO USER PARAMETERS i.e P_COA_ID AND P_CONC_REQUEST_ID...............

--FND FLEXSQL
--------------
BEGIN
SRW.USER_EXIT('FND SRWINIT'); }
SRW.USER_EXIT('FND FLEXSQL }
CODE="GL#" }-- THIS WE SHUD WRITE IN " BEFORE
REPORT TRIGGER
NUM=":P_COA_ID" }-- THIS IS FOR DISPLAYING STRUCTURE AND RETRIVE THE DATA FROM DATABASE
APPL_SHORT_NAME="SQLGL"
OUTPUT=":SELECT_ALL"
TABLE_ALIAS="GCC"
MODE="SELECT"
DISPLAY="ALL" ');
RETURN(TRUE);
END;
------------------------------------------------------------------------------------------------------------
NOW WE WANT TO DISPLAY THE DATA WHICH IS PRESENT IN THE STRUCTURE .... FOR THAT WE USE " FLEXID"

FOR THAT PLACE TWO FORMULA COLUMNS IN GROUP LEVEL AND NAME IT AS "CF_FLEXDATA" AND "CF_FLEXDESC"

DATATYPE -- CHAR
WIDTH --1000
--FND FLEXID
-------------
"CF_FLEXDATA--->"

SRW.USER_EXIT('FND FLEXIDVAL ---OBUL
CODE="GL#"
NUM=":P_COA_ID"
APPL_SHORT_NAME="SQLGL"
DATA=":FLEXDATA"
VALUE=":CF_DATA"
DISPLAY="ALL"
IDISPLAY="ALL" ');
RETURN(:CF_DATA); }
} -- THIS WE SHUD WRITE IN FORMAULA COLUMN WHICH NAMED AS :CF_FLEXDATA
}
}

-- THESE TWO FOR DISPLAY DATA WICH IS PRESENT IN DAT FLEXSQL

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

"CF_FLEXDESC"--->

SRW.USER_EXIT('FND FLEXIDVAL ---OBUL
CODE="GL#"
NUM=":P_COA_ID"
APPL_SHORT_NAME="SQLGL"
DATA=":FLEXDATA"
DESCRIPTION=":CF_DESC"
DISPLAY="ALL"
IDISPLAY="ALL" '); }
}
}-- THIS IS ALSO WE SHUD WRITE IN FORMULA COLUMN WHICH NAMED AS :CF_FLEXDESC

------ THESE 2 FORMULA COLUMNS WE SHUD TAKE IN GROUP LEVEL

for finding the CODE="GL#" -- GO TO APPLICATION DEVELOPER RESP---> FLEXFIELD-->REGISTER-->QUERY-->General Ledger

NOW AS USUAL WE REGISTER IT IN APPS.......

FOR DISPLAYING CHART_OF_ACCOUNTS_ID DYNAMICALLY WE SHUD USE DEFAULT TYPE AS SQL FRO P_COA_ID PARAMETER AND IN DEFAULT VALUE WRITE BELOW CODE...

SELECT CHART_OF_ACCOUNTS_ID FROM GL_SETS_OF_BO0OKS WHERE SET_OF_BOOKS_ID=:$PROFILES$.GL_SET_OF_BKS_ID

P2P(Procure-to-Pay) Cycle Tables with Joins

Pre Requisites for Purchase Order:

1.Item Creation:

2.Supplier Creation:

3.Buyer Creation:

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

1.Item Creation:

1)Attach the Responsibility called "Inventory Vision Operations (USA)"

2)Open the Items form

Items=>Master Item

3)Select the Organization name - Vision Operations

4)Enter the Item Name , Item Description

goto Inventory tab check the checkbox called Inventory

goto purchasing tab check the check box called Purchasing

5)Save

6)Goto Tools Menu => Organization Assignment option to assign for the multiple

organizations.

SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1='APACHE' --INVENTORY_ITEM_ID=20817

2.Supplier Creation:

1)Attach the Responsibility called "Purchasing Vision Operations (USA)"

2)Goto the Supplier Form

Supply Base=>Suppliers

3)Enter the Supplier Name . Save supplier number will be created automatically.

4)Select the Sites button enter the supplier site address and other details

5)Goto the Contacts tab enter the Contact details

Name

Phno

Postion and so on.....

SELECT * FROM PO_VENDORS WHERE SEGMENT1='20067' --VENDOR_ID=7930

SELECT * FROM PO_VENDOR_SITES_ALL WHERE VENDOR_ID=7930 --VENDOR_SITE_ID IN (4638,4639)

SELECT * FROM PO_VENDOR_CONTACTS WHERE VENDOR_SITE_ID IN (4638,4639)

3.Buyer Creation:

Buyer Creation:

==================

1)attach the Responsibility called "US HRMS Manager"

2)Create Employee

People => Enter and Maintain=>Select New button

3)Enter Emp name

select action option select "create Employement" select the optiona s "Buyer"

4)enter Data of Birth

save => Ok = > Empoyee number will be generated.

5)Goto System Administrator open the User form create or query user

select the Person field attach the emp name (Which we have created)

6)Save.

7)Goto Purchasing Responsibility

open the Buyers form

enter the employee name and save the transactions.

Setup => Personnal =>Buyers

SELECT * FROM PER_ALL_PEOPLE_F --WHERE --PERSON_ID='25'--EMPLOYEE_NUMBER='1289' --PERSON_ID=13496

Purchase Order Flow:

1.Requisition
2.RFQ(Request for Quatation)

1.REQUISITION:

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.

SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1='5716' --REQUISITION_HEADER_ID=56885

SELECT * FROM PO_REQUISITION_LINES_ALL WHERE REQUISITION_HEADER_ID=56885 --REQUISITION_LINE_ID=60797

SELECT * FROM PO_REQ_DISTRIBUTIONS_ALL WHERE REQUISITION_LINE_ID=60797

2.RFQ(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.

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='347' AND TYPE_LOOKUP_CODE='RFQ' --PO_HEADER_ID=32876

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=32876 -- PO_LINE_ID=38063

SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=38063 --LINE_LOCATION_ID=72425

3.QUOTATION:

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)

4.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.

Types of Purchase Order: -

	*Standard P.O*	*Planned P.O*	*Blanket P.O*	*ContractP.O*
Terms and Conditions Goods or Service Known Pricing Known Quantity known Account Distributions Known Delivery Schedule Known Can be Encumbered Can Encumber releases	Yes Yes Yes Yes Yes Yes Yes N/A	Yes Yes Yes Yes Yes May be Yes Yes	Yes Yes May be No No No No Yes	Yes No No No No No No N/A

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='4514' AND TYPE_LOOKUP_CODE='STANDARD' --PO_HEADER_ID =32878
--TYPE_LOOKUP_CODE

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID =32879 --PO_LINE_ID=38065

SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=38066 --LINE_LOCATION_ID=72427

SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE LINE_LOCATION_ID=72428

SELECT * FROM PO_LOOKUP_CODES --LOOKUP_CODE

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 .

5.RECEIPTS:

Receipts are one of the documents it will be used to find out how much quantity Supplier has supplied. We will find out Purchase Order status if it is successfully approved then we will create the Receipt. We will give the PO Number select Find button check the PO lines right mark and save. It will create the Receipt number select Header button it will shows the receipt number and date.

SELECT * FROM RCV_SHIPMENT_HEADERS WHERE RECEIPT_NUM=7472 --SHIPMENT_HEADER_ID=61421

SELECT * FROM RCV_SHIPMENT_LINES WHERE SHIPMENT_HEADER_ID=61421 -- SHIPMENT_LINE_ID=68368

SELECT * FROM RCV_TRANSACTIONS WHERE SHIPMENT_HEADER_ID=61421

Once Receipt will created go Inventory module and check whether the requested items are received or not by using these tables.

SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1='APACHE'

SELECT * FROM MTL_ONHAND_QUANTITIES WHERE INVENTORY_ITEM_ID=20817 AND ORGANIZATION_ID=204

SELECT SEGMENT1 FROM PO_HEADERS_ALL WHERE PO_HEADER_ID=32881 AND TYPE_LOOKUP_CODE='STANDARD'

Match Approval Level :- While creating the Purchase Order we will mention the Match Approval Level at Shipments we will have 3 types they are

2-way:- Purchase Order and Invoice Quantities must match within tolerance before the corresponding invoice can be paid.

3-way:- Purchase Order, Receipts and Invoice Quantities must match with in tolerance before the corresponding invoice can be paid.

4-way:- Purchase Order, Receipts, Inspection and Invoice Quantities must match with in tolerance before the corresponding invoice can be paid.

Account Payables (A.P) Module:-

Account payables will be used to do the payment transactions. A.P Module is integrated with both P.O and G.L Modules. In Account Payables we will create the invoices and we will approve once invoice is approved successfully we will make the payment. Once payment is over we will move the transactions from A.P to G.l.

1. Without supplier we cannot create Invoice.

2. Without invoice we cannot make Payment.

From the company point of view a person or Organization who is going to receive amount we will call as Supplier.

Types of Invoices:-

1. Standard

2. Credit Memo

3. Debit Memo

4. With Holding Tax

5. Po Default

6. Mixed

7. Pre Payment

8. Expense Report

9. Recurring Invoices

10. Quick Match

Standard Invoice:- We will create the Standard Invoice to particular Supplier and Supplier site we will enter the invoice amount, invoice date and soon……..

Credit Memo & Debit Memo Invoices:- Both Invoices has got negative (-ve) amount and adjusted against Standard Invoice. Credit Memo will be created whenever Supplier is giving discount. Debit Memo will be created if buyer is going to deduct the amount.

With Holding Tax Invoice:- If supplier is not registered supplier then buyer will make the Income Tax to the government on behalf of supplier.

Po Default Invoice:- Here we will create the Invoice as per Purchase Order amount. We will give the Po number system will retrieve PO amount and Invoice will be created as per PO details.

Prepayment Invoice:- When ever we want make payment to supplier in advance that tome we will create this Prepayment Invoice and we make the Payment.

Expense Reports Invoice:- It will be created for employee expenses as per the employee grade, position this Invoices will be calculated.

Recurring Invoice:- For some of the Invoices we will not be having supplier invoice that time we will create Recurring Invoices.

Ex:- For rent account we will be creating Invoice which has got fixed amount and fixed rate (duration).

Quick Match Invoice:- While creating Purchase Order we will be giving the match approval option as per that match approval we will create the Invoice and the Invoice type is Quick Match Invoice.

Mixed Invoice:- Mixed Invoices will be created for miscellaneous expenses. Once we create the invoice you have to do following 3 activities.

1. Validate Invoice

2. Approve the Invoice

3. Create Accounting entries for Invoice

INVOICES

Here we will select the Invoice type and we will give the Supplier number, name, site invoice date, invoice number, invoice currencies, and amount. Select Distributions button to distribute the Invoice amount into different accounts.

1. Invoice total should be equal to the distributions total then we will call it as Invoice validated successfully.

2. Select Actions…1 button chooses approve check box press OK then system will approve the Invoice.

3. Select Actions…1 button choose create accounting check box press OK button it will create the accounting entries we can see all this accounting transactions from tools view accounting option.

SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_NUM='INV4516' --INVOICE_ID=63379 ,--VENDOR_ID(LINK B/WAP INVOICE AND PO_VENDORS
)
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID=63379

Invoice Holds:- If invoice is not approved then that invoice will be keeping under hold status. By selecting holds button in invoice form we can see the holds details.

For view Invoice holds details:

Select * from ap_holds_all

For view release the Invoice holds names:

Select * from ap_holds_release_name_v;

PAYMENTS:

Payments:- Once the Invoice is approved then we can go for payments. The Payments are or 3 types. They were

1. Manual

2. Quick

3. Refund

Manual:- Here we will issue the checks manually to the supplier and we will capture that information in the payment scheme by using manual payment option.

Quick:- Through the Quick Payment type we can generate checks through the system and we can have the transactions directly in the system.

Refund:- When ever company is going to give advance back to the customer that time we will select payment type as Refund.

Navigation steps for Payments:-

payments ==> payments

For view list of payments:

Select * from ap_invoice_payments_all;

Select * from ap_payment_schedules_all;

For check’s information:

Select * from ap_checks_all;

For check format:

Select * from ap_check_formats;

Select * from ap_checkrun_conc_processes_all;

Distribution Set:- It is one of the option is available in Invoices Screen. While creating the Invoice we will attach distribution set. System will automatically create the transactions in distributions forms as per the distribution set.

Navigation:

set-up =>invoice=> distribution set

To view Distribution sets at header level:

Select * from ap_distribution_sets_all;

To view Distribution sets at lines level:

Select * from ap_distribution_set_lines_all;

Transferring Transactions from AP to GL:-

We will execute the concurrent program from SRS Window. This program will transfer all the payment transactions into the G.L Module. It will take following parameters.

Program Name:- Payables Transfer to General Ledger

Parameters:-

Set of Books Name

Transfer Reporting Book(s)

From Date

To Date

Journal Category

Validate Accounts

Transfer To GL Interface

Submit Journal Import : yes (It should be always YES)

To view from AP to GL:

Select * from gl_interface;

To view journal import details:

Select * from gl_je_headers à for Headers

Select * from gl_je_lines à for Lines

Select * from gl_je_batches à for Batches

To view posting:

Select * from gl_balances;

After submitting the request select viewà output button. It will shows number of transactions has been transferred to G.L. then select G.L Module (General Ledger, Vision Operations (USA)).

SELECT * FROM GL_JE_HEADERS

SELECT * FROM GL_JE_LINES

SELECT * FROM GL_JE_BATCHES

SELECT * FROM GL_BALANCES

O2C(Order-to-cash) Cycle Tables with Joins

SELECT * FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER='57088'

SELECT *FROM OE_ORDER_LINES_ALL OOL WHERE OOL.HEADER_ID=96882

SELECT *FROM WSH_NEW_DELIVERIES WHERE DELIVERY_ID=96911

SELECT *FROM WSH_DELIVERY_ASSIGNMENTS WHERE DELIVERY_DETAIL_ID=247230

SELECT * FROM WSH_DELIVERY_DETAILS WHERE SOURCE_HEADER_ID='96882'

SELECT *FROM WSH_LOOKUPS WHERE LOOKUP_TYPE='DELIVERY_STATUS'

SELECT *FROM WSH_LOOKUPS WHERE LOOKUP_TYPE='PICK_STATUS'

SELECT * FROM RA_INTERFACE_LINES_ALL
WHERE INTERFACE_LINE_ATTRIBUTE1 = '57088'

SELECT * FROM RA_INTERFACE_DISTRIBUTIONS_ALL
WHERE INTERFACE_LINE_ATTRIBUTE1 = '57088'

SELECT *FROM RA_CUSTOMER_TRX_ALL WHERE INTERFACE_HEADER_ATTRIBUTE1='57088' --10021210.

SELECT * FROM RA_CUSTOMER_TRX_LINES_ALL WHERE CUSTOMER_TRX_ID=210618

SELECT * FROM RA_CUST_TRX_LINE_GL_DIST_ALL WHERE CUSTOMER_TRX_LINE_ID=421425

SELECT *FROM AR_CASH_RECEIPTS_ALL WHERE RECEIPT_NUMBER='57088'

SELECT * FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE CASH_RECEIPT_ID = 33495

SELECT * FROM GL_JE_LINES WHERE JE_HEADER_ID=119398 AND JE_LINE_NUM=1

SELECT * FROM GL_JE_LINES
WHERE JE_HEADER_ID = 119395
AND JE_LINE_NUM = 12

SELECT * FROM RA_CUSTOMER_TRX_ALL
WHERE INTERFACE_HEADER_ATTRIBUTE1 = '57087'

FND LOAD Syntax

FND Load Syntax for CP:
-----------------------------------------
FNDLOAD apps/apps 0 Y DOWNLOAD /apps/aptest/visappl/fnd/11.5.0/patch/115/import/afcpprog.lct TEST.ldt PROGRAM APPLICATION_SHORT_NAME="PO"

FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL /apps/aptest/visappl/fnd/11.5.0/patch/115/import/afcpprog.lct TEST.ldt PROGRAM PROGRAM_NAME="XX_TEST_FND_LOAD"

All Back-end Tables

FOR FORMS
==========

SELECT * FROM FND_FORM WHERE FORM_NAME = 'EMPANDDEPT' --ALL FORMS

SELECT * FROM FND_FORM_FUNCTIONS WHERE FORM_ID=59986 --ALL FUNCTIONS

SELECT TNAME FROM TAB WHERE TNAME LIKE 'FND_PROF%'

SELECT * FROM FND_MENUS_VL WHERE MENU_NAME='FORMS MENU' --ALL MENUS

SELECT * FROM FND_RESPONSIBILITY_VL WHERE MENU_ID=1014197 --ALL RESPONSIBILITIES

FOR REPORTS
=============

SELECT * FROM FND_EXECUTABLES WHERE EXECUTABLE_NAME='GL_LOAD' -- FOR EXECUTABLES

SELECT * FROM FND_CONCURRENT_PROGRAMS_VL WHERE EXECUTABLE_ID=13237 --FOR CONCURRENT PROGRAMS

SELECT * FROM FND_REQUEST_GROUPS WHERE APPLICATION_ID=20064 AND REQUEST_GROUP_NAME='All Reports' --FOR REQUEST GROUPS

SELECT * FROM FND_REQUEST_GROUP_UNITS --> Concurrent programs assigned to Request group

SELECT * FROM FND_RESPONSIBILITY WHERE REQUEST_GROUP_ID=1001313 --FOR RESPONSE

SELECT * FROM FND_USER --FOR USERS

SELECT * FROM FND_CONCURRENT_REQUESTS --USER_ID=REQUESTED_BY

SELECT * FROM FND_USER_RESP_GROUPS_DIRECT

VALUESETS
=========

SELECT * FROM FND_FLEX_VALUE_SETS --> VALUE SET INFO

SELECT * FROM FND_FLEX_VALUES --> VALUES ASSIGNED TO THE VALUESET

FOR FLEXFIELDS
===============

SELECT * FROM FND_DESCRIPTIVE_FLEXS_VL WHERE TITLE = 'Requisition Headers'--FOR DESCRIPTIVE FLEXFIELDS

SELECT * FROM FND_ID_FLEXS WHERE ID_FLEX_CODE='GL#' --FOR NAME OF THE KEY FLEXFIELDS

SELECT * FROM FND_ID_FLEX_STRUCTURES WHERE ID_FLEX_CODE ='GL#' AND ID_FLEX_STRUCTURE_CODE='OPERATIONS_ACCOUNTING_FLEX'
--FOR KEY FLEXFIELDS(CONTAINS STRUCTURES)

FOR PROFILES
============

SELECT * FROM FND_PROFILE_OPTIONS_VL WHERE USER_PROFILE_OPTION_NAME ='MO: Operating Unit' --FOR PROFILES

HOW TO FIND OUT HOWMANY RESP_NAMES FOR PARTICULAR USER
======================================================

SELECT FU.USER_NAME,
FR.RESPONSIBILITY_NAME
FROM FND_USER FU,
FND_RESPONSIBILITY_VL FR,
FND_USER_RESP_GROUPS_DIRECT FUR
WHERE FU.USER_ID=FUR.USER_ID
AND FUR.RESPONSIBILITY_ID=FR.RESPONSIBILITY_ID
AND FU.USER_NAME='OPERATIONS'

HOW TO FIND OUT THE LIST OF CONC_PROGRAMS EXECUTED BY USER FOR DAY
==================================================================

SELECT FE.EXECUTABLE_NAME,
FE.EXECUTION_FILE_NAME,
FCP.CONCURRENT_PROGRAM_NAME,
FCP.USER_CONCURRENT_PROGRAM_NAME,
FRG.REQUEST_GROUP_NAME,
FR.RESPONSIBILITY_NAME
FROM FND_USER FU,
FND_CONCURRENT_REQUESTS FCR,
FND_CONCURRENT_PROGRAMS_VL FCP,
FND_EXECUTABLES FE,
FND_RESPONSIBILITY_VL FR,
FND_REQUEST_GROUPS FRG
WHERE FU.USER_ID=FCR.REQUESTED_BY
AND FCR.CONCURRENT_PROGRAM_ID=FCP.CONCURRENT_PROGRAM_ID
AND FCP.EXECUTABLE_ID = FE.EXECUTABLE_ID
AND FCR.RESPONSIBILITY_ID=FR.RESPONSIBILITY_ID
AND FRG.REQUEST_GROUP_ID=FR.REQUEST_GROUP_ID
AND TO_DATE(FCR.REQUEST_DATE)=TO_DATE(SYSDATE)
AND FU.USER_NAME='OPERATIONS'

PURCHASE ORDER :
--------------
1. PO_REQUISITION_HEADERS_ALL --> REQUISITION HEADER INFO
2. PO_REQUISITION_LINES_ALL --> REQUISITION LINES INFO
3. PO_REQ_DISTRIBUTIONS_ALL --> REQUISITION DISTRIBUTION INFO
4. PO_HEADERS_ALL --> PO HEADER INFO
5. PO_LINES_ALL --> PO LINES INFO
6. PO_LINE_LOCATIONS_ALL --> PO LINE SHIPMENT INFO
7. PO_DISTRIBUITIONS_ALL --> PO ACCOUNTING INFO
8. RCV_SHIPMENT_HEADERS_ALL --> RECEIVING HEADER INFO
9. RCV_SHIPMENT_LINES_ALL --> RECEIVING LINES INFO
10.RCV_TRANSACTIONS --> RECEIVING TRANSACTIONS INFO
11.PO_VENDORS --> VENDOR INFO
12.PO_VENDOR_SITES_ALL --> VENDOR SITE INFO
13.PO_VENDOR_CONTACTS --> VENDOR CONTACT INFO
14.HR_LOCATIONS --> SUPPLIER PHYSICAL ADDRESS INFO
15.PO_LOOKUPS --> PO LOOKUP INFO

ACCOUNTS PAYABLE :
----------------
1. AP_INVOICES_ALL --> INVOICE HEADER INFO
2. AP_INVOICE_DISTRIBUTIONS_ALL --> INVOICE LINES INFO
3. AP_TERMS --> PAYMENT TERMS INFO
4. AP_PAYMENT_SCHEDULES_ALL --> PAYMENT SCHEDULE INFO
5. AP_INVOICE_PAYMENTS_ALL --> INVOICE PAYMENT INFO
6. AP_CHECKS_ALL --> CHECK INFO
7. AP_ACCOUNTING_EVENTS_ALL --> INVOICE ACCOUNTING EVENT INFO
8. AP_AE_HEADERS_ALL --> ACCOUNTING HEADER INFO
9. AP_AE_LINES_ALL --> ACCOUNTING LINES INFO
10.AP_BANKS --> SUPPLIER BANK INFO
11.AP_BANK_BRANCHES --> SUPPLIER BANK BRANCHES
12.AP_LOOKUPS --> INVOCE LOOKUPS
13.PO_VENDORS --> VENDOR INFO
14.PO_VENDOR_SITES_ALL --> VENDOR SITE INFO
15.PO_VENDOR_CONTACTS --> VENDOR CONTACT INFO
16.HR_LOCATIONS --> SUPPLIER PHYSICAL ADDRESS INFO

ORDER MANAGEMENT :
-----------------
1. OE_ORDER_HEADERS_ALL --> ORDER HEADER INFO
2. OE_ORDER_LINES_ALL --> ORDER LINES INFO
3. OE_TRANSACTION_TYPE_TL --> ORDER TYPES INFO
4. QP_LIST_HEADERS_TL --> PRICE LIST NAME
5. RA_SALESREPS_ALL --> SALES PERSONS INFO
6. WSH_DELIVERY_DETAILS --> DELIVERY DETAILS INFO
7. WSH_NEW_DELIVERIES --> DELIVERY HEADER INFO
8. WSH_DELIVERY_ASSIGNMENTS --> DELIVERY ASSIGNMENTS INFO
9. WSH_TRIPS --> DELIVERY TRIPS INFO
10.OE_HOLDS_ALL --> ORDER HOLDS INFO
11.OE_HOLD_SOURCES_ALL --> ORDER HOLD SOURCE INFO
12.OE_HOLD_DEFINITIONS_ALL --> ORDER HOLD DEFINITIONS INFO
13.OE_HOLD_RELEASES_ALL --> ORDER HOLD RELEASE INFO
14.HZ_CUST_ACCOUNTS --> CUST ACCOUT INFO
15.HZ_PARTIES --> PARTY INFO
16.HZ_CUST_SITE_USES_ALL --> CUSTOMER SITE INFO
17.HZ_CUST_ACCT_SITES_ALL --> CUSTOMER SITE ACCOUNT INFO
18.HZ_PARTY_SITES --> PARTY SITE INFO
19.HZ_LOCATIONS --> CUSTOMER PHYSICAL ADDRESS INFO

ACCOUNTS RECEIVABLE :
-------------------
1. RA_CUSTOMER_TRX_ALL --> TRANSACTION HEADER INFO
2. RA_CUSTOMER_TRX_LINES_ALL --> TRANSACTION LINES INFO
3. RA_CUST_TRX_LINE_GL_DIST_ALL --> TRANSACTION DISTRIBUTION INFO
4. AR_PAYMENT_SCHEDULES_ALL --> CUSTOMER PAYMENT SCHEDULE INFO
5. RA_CUST_TRX_TYPES_ALL --> TRANSACTION TYPE INFO
6. AR_CASH_RECEIPTS_ALL --> RECEIPT HEADER INFO
7. AR_RECEIVABLE_APPLICATIONS_ALL --> RECEIPT APPLIED INFO
8. AR_RECEIPT_METHODS --> RECEIPT METHOD INFO
9. AR_INTERIM_CASH_RECEIPTS_ALL --> INTERIM RECEIPTS INFO
10.HZ_CUST_ACCOUNTS --> CUST ACCOUT INFO
11.HZ_PARTIES --> PARTY INFO
12.HZ_CUST_SITE_USES_ALL --> CUSTOMER SITE INFO
13.HZ_CUST_ACCT_SITES_ALL --> CUSTOMER SITE ACCOUNT INFO
14.HZ_PARTY_SITES --> PARTY SITE INFO
15.HZ_LOCATIONS --> CUSTOMER PHYSICAL ADDRESS INFO

INVENTORY MODULUE :
------------------
1. MTL_SYSTEM_ITEMS_B --> ITEM INFO
2. MTL_ONHAND_QUANTITIES --> ITEM ONHAND QTY INFO
3. MTL_RESERVATIONS --> ITEM RESERVATIONS INFO
4. MTL_MATERIAL_TRANSACTIONS --> ITEM TRANSACTIONS INFO
5. MTL_TRANSACTION_TYPES --> ITEM TRANSACTION TYPES INFO
6. MTL_TXN_SOURCE_TYPES --> ITEM TRANSACTION SOURCE INFO
7. MTL_TXN_REQUEST_HEADERS --> MOVE ORDER HEADER INFO
8. MTL_TXN_REQUEST_LINES --> MOVE ORDER LINES INFO
9. MTL_ITEM_LOCATORS --> ITEM LOCATOR INFO
10.MTL_CATEGORIES --> INVENTORY CATEGORIES
11.MTL_ITEM_CATEGORIES --> INVENTORY ITEM CATEGORIES
12.MTL_PARAMETERS --> INVENTORY PARAMETERS
13.ORG_ORGANIZATION_DEFINITIONS --> ORGANIZATION INFO
14.MTL_SECONDARY_INVENTORIES --> SUBINVENTORY INFO

GENERAL LEDGER (GL) :
-------------------
1. GL_JE_HEADERS -- JOURNAL HEADER INFO
2. GL_JE_LINES -- JOURNAL LINES INFO
3. GL_JE_BATCHES -- JOURNAL BATCH INFO
4. GL_BALANCES -- JOURNAL BALANCES
5. GL_SETS_OF_BOOKS -- SET OF BOOKS INFO
6. GL_CHART_OF_ACCOUNTS -- CHART OF ACCOUNTS INFO
7. GL_CODE_COMBINATIONS -- CODE COMBINATIONS INFO
8. GL_JE_SOURCES -- JOURNAL SOURCE INFO
9. GL_JE_CATEGORIES -- JOURNAL CATEGORY INFO
10.GL_DAILY_RATES -- DAILY RATES INFO
11.FND_CURRENCIES -- CURRENCY INFO
12.GL_PERIODS -- ACCOUNTING PERIODS INFO
13.GL_PERIOD_SETS -- ACCOUNTING PERIOD SET INFO

R12 Features,

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

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

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

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

Accounts Payable :
----------------
1.ap_invoice_lines_all table added in R12.
2.ap_invoice_distributions_all table populates the data
when ever invoice gets accounted.
3.Supplier form was converted from form based solution to
webbased solution.
4.po_vendors tables replaced with ap_suppliers tables.
5.Supplier and customer information was defined together
Under TCA(Trading Community Architecture).
6.Accounting Tables were modified.
7.Both supplier and customer bank information was defined
under payments(New application in R12) Application.

11i Table R12 Table
----------- ------------
po_vendors ap_suppliers
po_vendor_sites_all ap_supplier_sites_all
po_vendor_contacts ap_supplier_contacts
ap_banks ce_banks
ap_bank_branches ce_bank_branches
ap_ae_headers_all xla_ae_headers
ap_ae_lines_all xla_ae_lines
ap_ae_accounting_events xla_events

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

hz_cust_accounts
hz_parties
hz_cust_site_uses_all
hz_cust_acct_sites_all
hz_party_sites
hz_locations

Registration Of Table In APPS

SQL> create table xx_po_emp(empno number(10),
2 ename varchar2(100),
3 sal number(10),
4 job varchar2(100),
5 hiredate date,
6 deptno number(2)
7 )
8 /

Table created.

SQL> grant all on xx_po_emp to apps;

Grant succeeded.

SQL> conn apps/apps@vis
Connected.
SQL> create public synonym xx_po_emp for po.xx_po_emp;

Synonym created.

AD_DD(This is a Package) and this is used to register the table as well as columns in Apps.

For register table : ad_dd.register_table
register column: ad_dd.register_column

SQL> exec ad_dd.register_table('PO','XX_PO_EMP','T');

PL/SQL procedure successfully completed.

SQL> exec ad_dd.register_column('PO','XX_PO_EMP','EMPNO',1,'NUMBER',10,'N','Y');

PL/SQL procedure successfully completed.

SQL> exec ad_dd.register_column('PO','XX_PO_EMP','ENAME',2,'VARCHAR2',100,'N','Y');

PL/SQL procedure successfully completed.

SQL> exec ad_dd.register_column('PO','XX_PO_EMP','SAL',3,'NUMBER',10,'N','Y');

PL/SQL procedure successfully completed.

SQL> exec ad_dd.register_column('PO','XX_PO_EMP','JOB',4,'VARCHAR2',100,'N','Y');

PL/SQL procedure successfully completed.

SQL> exec ad_dd.register_column('PO','XX_PO_EMP','HIREDATE',5,'DATE',11,'N','Y');

PL/SQL procedure successfully completed.

SQL> exec ad_dd.register_column('PO','XX_PO_EMP','DEPTNO',6,'NUMBER',2,'N','Y');

PL/SQL procedure successfully completed.

SQL> COMMIT
2 /

Commit complete.

Complete AP Interface

Pre-requisites:

a) Vendors and their Sites Should Be Setup
b) Currency and exchange Rates Should Be setup
c) Source and invoice type lookup code must be defined
d) Account Payables Account and Distribution Account must be defined.
e) Payment Terms should be defines and GL Period must be in opened status.

Interface Tables:

AP_INVOICES_INTERFACE
AP_INVOCE_LINES_INTERFACE

BASE TABLES :

AP_INVOICES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL
AP_PAYMENT_SCHEDULES

INTERFACE PROGRAM:

Payables Open Interface Import

Parameters to be passed during the program:

Source = External
group_id = As Specified in the data file
Batch Name = N/A
Hold Name = null
Hold Reason = null
gl date = null
Purge = No
Summarize = No

Error Tables:

AP_INTERFACE_REJECTIONS
AP_INTERFACE_CONTROLS

Main Columns In AP_INVOICES_INTERFACE:

INVOICE_ID, INVOICE_NUM, INVOICE_DATE ,INVOICE_AMOUNT
VENDOR_ID , VENDOR_NUM, VENDOR_NAME ,VENDOR_SITE_CODE, VENDOR_SITE_ID
TERMS_NAME, TERM_ID, TERM_DATE, SOURCE, PAYMENT_METHOD_LOOKUP_CODE,
GL_DATE, PO_NUMBER, INVOICE_CURRENCE_CODE, EXCHANGE_RATE_TYPE
VOUCHER_NUMBER, PAYMENT_CURRENCY_CODE, DOC_CATEGPRY_CODE

Main Columns In AP_INVOICE_LINES_INTERFACE:

INVOICE_ID , INVOICE_LINE_ID , LINE_NUM, LINE_TYPE_LOOKUP_CODE
AMOUNR , DIST_CODE_COMBINATION_ID, DIST_CODE_CONCATENATED
PO_HEADER_ID, PO_LINE_ID, PO_LINE_NUMBER

Tables To Be Used For Validation:

PO_VENDORS AP_TERMS ORG_ORGANIZATION_DEFINITIONS
FND_CURRNECIES AP_INVOICES_ALL GL_DAILY_CONVERSION_TYPES
GL_CODE_COMBINATIONS AP_LOOKUP_CODES GL_PERIOD_STATUSES

Control file for Headers:-

OPTIONS(SKIP=1)

LOAD DATA

INFILE '/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_temp.csv'

truncate into table ap_int_temp

fields terminated by ',' optionally enclosed by '"'

trailing nullcols

(

INVOICE_NUM ,

INVOICE_DATE ,

INVOICE_TYPE_LOOKUP_CODE ,

VENDOR_NAME ,

VENDOR_SITE_CODE ,

INVOICE_AMOUNT ,

INVOICE_CURRENCY_CODE ,

DESCRIPTION ,

SOURCE ,

PAYMENT_METHOD_LOOKUP_CODE ,

PAY_GROUP_LOOKUP_CODE ,

OU_NAME "REPLACE(:OU_NAME,CHR(13),'')"

)

For Lines:-

OPTIONS(SKIP=1)

LOAD DATA

INFILE '/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_line_temp.csv'

truncate into table ap_int_line_temp

fields terminated by ',' optionally enclosed by '"'

trailing nullcols

(

INVOICE_NUM ,

OU_NAME ,

LINE_NUMBER ,

LINE_TYPE_LOOKUP_CODE ,

AMOUNT ,

DESCRIPTION ,

DIST_CODE_CONCATENATED "REPLACE(:DIST_CODE_CONCATENATED,CHR(13),'')"

)

Shell Script:-

sqlldr apps/apps control='/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_temp.ctl' log='/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_temp.log'

sqlldr apps/apps control='/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_line_temp.ctl' log='/apps/aptest/visappl/xxcus/11.5.0/bin/ap_int_line_temp.log'

exit 0

Common error table and procedure:-

create table ap_common_err_tab

(

interface_name varchar2(50),

table_name varchar2(50),

column_name varchar2(50),

error_message varchar2(100),

created_by varchar2(10),

creation_date date,

last_updated_by varchar2(10),

last_update_date date,

request_id varchar2(10)

);

create or replace procedure ap_common_err_proc

(

p_interface_name varchar2,

p_table_name varchar2,

p_column_name varchar2,

p_error_message varchar2

)

begin

insert into ap_common_err_tab

values

(

p_interface_name,

p_table_name,

p_column_name,

p_error_message,

fnd_profile.value('USER_ID'),

SYSDATE,

fnd_profile.value('USER_ID'),

SYSDATE,

fnd_profile.value('CONC_REQUEST_ID')

);

Total Package With Validation:-

CREATE OR REPLACE package body APPS.ee_ap_inv_pkg

--for declaring global variables

g_user_id number(10):=fnd_profile.value('USER_ID');

g_date date:=sysdate;

procedure ap_record_details

cursor c1 is select * from ap_inv_head

where process_flag='S';

cursor c2 is select * from ap_inv_head

where process_flag='E';

v_tot_rec number;

v_sus_rec number;

v_err_rec number;

begin

--for total records

select count(*) into v_tot_rec

from ap_inv_head;

-- for success records

select count(*) into v_sus_rec

from ap_inv_head

where process_flag='S';

--for error records

select count(*) into v_err_rec

from ap_inv_head

where process_flag='E';

for info in c1

loop

fnd_file.put_line(fnd_file.output,'---------------------------');

fnd_file.put_line(fnd_file.output,'FOR SUSCESS INFORMATION');

fnd_file.put_line(fnd_file.output,'---------------------------');

fnd_file.put_line(fnd_file.output,'INVOICE NUM =>'||info.invoice_num);

fnd_file.put_line(fnd_file.output,'VENDOR NAME =>'||info.vendor_name);

fnd_file.put_line(fnd_file.output,'TOTAL RECORDS =>'||v_tot_rec);

fnd_file.put_line(fnd_file.output,'TOTAL SUCESS RECORDS =>'||v_sus_rec);

fnd_file.put_line(fnd_file.output,'TOTAL ERROR RECORDS =>'||v_err_rec);

end loop;

for info1 in c2

loop

fnd_file.put_line(fnd_file.output,'---------------------------');

fnd_file.put_line(fnd_file.output,'FOR ERROR INFORMATION');

fnd_file.put_line(fnd_file.output,'---------------------------');

fnd_file.put_line(fnd_file.output,'INVOICE NUM =>'||info1.invoice_num);

fnd_file.put_line(fnd_file.output,'VENDOR NAME =>'||info1.vendor_name);

fnd_file.put_line(fnd_file.output,'TOTAL RECORDS =>'||v_tot_rec);

fnd_file.put_line(fnd_file.output,'TOTAL SUCESS RECORDS =>'||v_sus_rec);

fnd_file.put_line(fnd_file.output,'TOTAL ERROR RECORDS =>'||v_err_rec);

fnd_file.put_line(fnd_file.output,'ERROR MESSAGE =>'||info1.error_message);

end loop;

end ap_record_details;

---arch table information

procedure arch_proc

begin

insert into ap_inv_head_arch select * from ap_inv_head;

insert into ap_inv_line_arch select * from ap_inv_line;

delete from ap_inv_head;

delete from ap_inv_line;

commit;

end;

procedure dis_log(p_msg in varchar2)

begin

fnd_file.put_line(fnd_file.log,p_msg);

end dis_log;

procedure main(errbuf out varchar2,

retcode out varchar2

)

cursor head is select * from ap_inv_head;

cursor line(p_inv_num in varchar2) is select * from ap_inv_line

where invoice_num=p_inv_num;

v_head ap_invoices_interface%rowtype;

v_line ap_invoice_lines_interface%rowtype;

v_head_tab varchar2(100):='AP_INV_HEAD';

v_line_tab varchar2(100):='AP_INV_LINE';

v_conc_program varchar2(100);

v_process_flag varchar2(1);

v_error_message varchar2(100);

v_tot_err_msg varchar2(1000);

v_count number;

v_inv_num_exists exception;

v_amount number;

begin

---concurrent program------

select fcp.user_concurrent_program_name

into v_conc_program

from fnd_concurrent_programs_vl fcp,

fnd_concurrent_requests fcr

where fcp.concurrent_program_id=fcr.concurrent_program_id

and fcr.request_id=g_request_id;

---end of concurrent program------

for rec_head in head

loop

begin

v_process_flag:='S';

v_error_message:=null;

v_tot_err_msg:=null;

--*****invoice type lookyp code****--

begin

select lookup_code into v_head.invoice_type_lookup_code

from ap_lookup_codes

where lookup_type='INVOICE TYPE'

and displayed_field=rec_head.invoice_type_lookup_code;

exception

when no_data_found then

v_process_flag:='E';

v_error_message:='INVALID INVOICE TYPE LOOKUP CODE =>'||rec_head.invoice_type_lookup_code;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_head_tab,

p_column_name => 'INVOICE TYPE LOOKYP CODE',

p_error_message => v_error_message

);

when others then

v_process_flag:='E';

v_error_message:='EXCEPTION AT INVOICE TYPE LOOKUP CODE VALIDATION =>'||rec_head.invoice_type_lookup_code||' '||sqlerrm;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_head_tab,

p_column_name => 'INVOICE TYPE LOOKYP CODE',

p_error_message => v_error_message

);

end;

--*****vendor name validation *****---

begin

select vendor_id into v_head.vendor_id

from po_vendors

where vendor_name=rec_head.vendor_name;

exception

when no_data_found then

v_process_flag:='E';

v_error_message:='INVALID VENDOR NAME =>'||rec_head.vendor_name;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_head_tab,

p_column_name => 'VENDOR_NAME',

p_error_message => v_error_message

);

when others then

v_process_flag:='E';

v_error_message:='EXCEPTION AT VENDOR_NAME VALIDATION =>'||rec_head.vendor_name||' '||sqlerrm;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_head_tab,

p_column_name => 'VENDOR_NAME',

p_error_message => v_error_message

);

end;

---*****organiozation name validation ********---

begin

select organization_id into v_head.org_id

from org_organization_definitions

where organization_name=rec_head.ou_name;

exception

when no_data_found then

v_process_flag:='E';

v_error_message:='INVALID ORG NAME =>'||rec_head.ou_name;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_head_tab,

p_column_name => 'ORGANIZATION NAME',

p_error_message => v_error_message

);

when others then

v_process_flag:='E';

v_error_message:='EXCEPTION AT ORG_NAME VALIDATION =>'||rec_head.ou_name||' '||sqlerrm;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_head_tab,

p_column_name => 'ORGANIZATION NAME',

p_error_message => v_error_message

);

end;

---*****vendor site code validation *******------

begin

select vendor_site_id into v_head.vendor_site_id

from po_vendor_sites_all

where vendor_site_code=rec_head.vendor_site_code

and vendor_id=v_head.vendor_id

and org_id=v_head.org_id;

exception

when no_data_found then

v_process_flag:='E';

v_error_message:='INVALID VENDOR SITE CODE =>'||rec_head.vendor_site_code;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_head_tab,

p_column_name => 'VENDOR_SITE_CODE',

p_error_message => v_error_message

);

when others then

v_process_flag:='E';

v_error_message:='EXCEPTION AT ORG_NAME VALIDATION =>'||rec_head.vendor_site_code||' '||sqlerrm;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_head_tab,

p_column_name => 'VENDOR_SITE_CODE',

p_error_message => v_error_message

);

end;

--*******invoice num validation****---

select count(1) into v_count

from ap_invoices_all

where invoice_num=rec_head.invoice_num

and vendor_id=v_head.vendor_id

and org_id=v_head.org_id;

if v_count > 0

then

raise v_inv_num_exists;

end if;

-----****source validation****-----

begin

select lookup_code into v_head.source

from ap_lookup_codes

where lookup_type='SOURCE'

and displayed_field=rec_head.source;

exception

when no_data_found then

v_process_flag:='E';

v_error_message:='INVALID SOURCE =>'||rec_head.source;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_head_tab,

p_column_name => 'SOURCE',

p_error_message => v_error_message

);

when others then

v_process_flag:='E';

v_error_message:='EXCEPTION AT SOURCE VALIDATION =>'||rec_head.source||' '||sqlerrm;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_head_tab,

p_column_name => 'SOURCE',

p_error_message => v_error_message

);

end;

---***terms validation ***----

begin

select term_id into v_head.terms_id

from ap_terms

where name=rec_head.terms_name;

exception

when no_data_found then

v_process_flag:='E';

v_error_message:='INVALID TERMS NAME =>'||rec_head.terms_name;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_head_tab,

p_column_name => 'TERMS_NAME',

p_error_message => v_error_message

);

when others then

v_process_flag:='E';

v_error_message:='EXCEPTION AT TERMS NAME VALIDATION =>'||rec_head.terms_name||' '||sqlerrm;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_head_tab,

p_column_name => 'TERMS_NAME',

p_error_message => v_error_message

);

end;

---**** invoice amount validation ****----

begin

select sum(amount) into v_amount

from ap_inv_line

where invoice_num=rec_head.invoice_num;

if rec_head.invoice_amount <> v_amount then

v_process_flag:='E';

v_error_message:='INVOICE AMOUNT DOES NOT MATCH YOUR LINE AMOUNT =>'||rec_head.invoice_amount;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_head_tab,

p_column_name => 'INVOICE AMOUNT',

p_error_message => v_error_message

);

end if;

end;

select ap_invoices_interface_s.nextval into v_head.invoice_id from dual;

v_head.invoice_num := rec_head.invoice_num;

-- v_head.invoice_type_lookup_code := rec_head.invoice_type_lookup_code;

v_head.vendor_name := rec_head.vendor_name;

v_head.vendor_site_code := rec_head.vendor_site_code;

v_head.invoice_amount := rec_head.invoice_amount;

v_head.terms_name := rec_head.terms_name;

v_head.created_by := g_user_id;

v_head.creation_date := g_date;

v_head.last_updated_by := g_user_id;

v_head.last_update_date := g_date;

for rec_line in line(rec_head.invoice_num)

loop

---*** line type validation ***----

begin

select lookup_code into v_line.line_type_lookup_code

from ap_lookup_codes

where lookup_type ='INVOICE DISTRIBUTION TYPE'

and displayed_field=rec_line.line_type_lookup_code;

exception

when no_data_found then

v_process_flag:='E';

v_error_message:='INVALID LINE TYPE LOOKUP CODE =>'||rec_line.line_type_lookup_code;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_line_tab ,

p_column_name => 'LINE TYPE LOOKUP CODE',

p_error_message => v_error_message

);

when others then

v_process_flag:='E';

v_error_message:='EXCEPTION AT LINE TYPE LOOKUP CODE VALIDATION =>'||rec_line.line_type_lookup_code||' '||sqlerrm;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_line_tab ,

p_column_name => 'LINE TYPE LOOKUP CODE',

p_error_message => v_error_message

);

end;

---*** charge account valiation ***------

begin

select gcc.code_combination_id into v_line.dist_code_combination_id

from gl_code_combinations_kfv gcc,

gl_sets_of_books gsb

where gcc.chart_of_accounts_id=gsb.chart_of_accounts_id

and gsb.set_of_books_id=fnd_profile.value('GL_SET_OF_BKS_ID')

and gcc.concatenated_segments=rec_line.dist_code_concatenated;

exception

when no_data_found then

v_process_flag:='E';

v_error_message:='INVALID CHARGE ACCOUNT =>'||rec_line.dist_code_concatenated;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_line_tab ,

p_column_name => 'CHARGE ACCOUNT',

p_error_message => v_error_message

);

when others then

v_process_flag:='E';

v_error_message:='EXCEPTION AT CHARGE ACCOUNT VALIDATION =>'||rec_line.dist_code_concatenated||' '||sqlerrm;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_line_tab ,

p_column_name => 'CHARGE ACCOUNT',

p_error_message => v_error_message

);

end;

select ap_invoice_lines_interface_s.nextval into v_line.invoice_line_id from dual;

v_line.invoice_id := v_head.invoice_id;

v_line.line_number := rec_line.line_number;

--v_line.line_type_lookup_code := rec_line.line_type_lookup_code;

v_line.amount := rec_line.amount;

v_line.dist_code_concatenated := rec_line.dist_code_concatenated;

v_line.created_by := g_user_id;

v_line.creation_date := g_date;

v_line.last_updated_by := g_user_id;

v_line.last_update_date := g_date;

v_line.org_id := v_head.org_id;

dis_log('PROCESS_FLAG =>'||v_process_flag);

if v_process_flag = 'S' then

insert into ap_invoice_lines_interface values v_line;

end if;

end loop;

dis_log('PROCESS_FLAG =>'||v_process_flag);

if v_process_flag = 'S' then

insert into ap_invoices_interface values v_head;

end if;

update ap_inv_head set process_flag=v_process_flag,

error_message=v_tot_err_msg

where invoice_num=rec_head.invoice_num;

exception

when v_inv_num_exists then

v_process_flag:='E';

dis_log('PROCESS_FLAG =>'||v_process_flag);

v_error_message:='INVOICE NUM ALREADY EXISTS =>'||rec_head.invoice_num;

v_tot_err_msg:=v_tot_err_msg||' '||v_error_message;

ee_ap_comm_err_procedure(p_interface_name => v_conc_program,

p_table_name => v_head_tab,

p_column_name => 'INVOICE NUM',

p_error_message => v_error_message

);

update ap_inv_head set process_flag=v_process_flag,

error_message=v_tot_err_msg

where invoice_num=rec_head.invoice_num;

end;

end loop;

ap_record_details; --for records information

arch_proc ; --for arch tables information

exception

when others then

dis_log('EXCEPTION AT PACKAGE =>'||sqlerrm);

end main;

end ee_ap_inv_pkg;

Search This Blog

Wednesday 6 June 2012

For Registration of Reports,Pl/SQL Procedures,SQL * Plus... etc from Back end By using PL/SQL Procedure And code shown below.

QUERY:-

Pre Requisites for Purchase Order:

1.Item Creation:

2.Supplier Creation:

3.Buyer Creation:

1.Item Creation:

2.Supplier Creation:

3.Buyer Creation:

Purchase Order Flow:

Control file for Headers:-

For Lines:-

Shell Script:-

Common error table and procedure:-

Total Package With Validation:-