The query returns the cross reference for an item in oracle inventory.Modify the table name and column name where required to concatenate the multiple rows into single row and single column.
select substr(cross_reference,2) cross_reference
from (
select inventory_item_id, max(sys_connect_by_path(cross_reference,',')) cross_reference
from (
select inventory_item_id, cross_reference, row_number() over (partition by inventory_item_id order by inventory_item_id ) rnum
from mtl_cross_references_b where inventory_item_id=iid
)
start with rnum = 1
connect by prior rnum = rnum-1 and prior inventory_item_id = inventory_item_id
group by inventory_item_id
order by inventory_item_id
)order by inventory_item_id;
Here iid is inventory item id for an items.
Wednesday, September 23, 2009
Monday, September 14, 2009
Decrypting Password
CREATE OR REPLACE FUNCTION HH_TERM_DECRYPT
/********************************************************************
* FILE NAME
* HH_TERM_DECRYPT.sql
*
*
* DESCRIPTION
* This Oracle function will decrypt the Oracle Apps password from
* a given user. The function can be called from a SQL command line
* by issuing the following command:
*
* SELECT HH_term_decrypt('HHHHHH') FROM DUAL;
*
* Where HHHHHH is the username of which the password is required.
* For this function to be able to work correctly a modification to
* the package specification of FND_WEB_SEC is needed. Please add
* the following line to the FND_WEB_SEC package:
*
* function decrypt(key in varchar2, value in varchar2) return varchar2;
*
* A more detailed description of this Oracle Password exploit can be
* found at the weblog of Johan Louwers: http://johanlouwers.blogspot.com/
* More information about Oracle security can be found at his website
* at http://www.terminalcult.org/
*
*
*
*
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* http://www.gnu.org/licenses/gpl.txt
*
*
* DOWNLOAD / CONTACT
* you can download the script from the following location:
* http://www.terminalcult.org/source/oracle/sql/HH_TERM_DECRYPT.sql
*
* You can contact Johan Louwers at Johan.Louwers(at)terminalcult.org
*
*
* HISTORY
* Version Date Author(s) Description
* ------- ----------- ------------------- --------------------------
* 1.0 02-JAN-2007 Johan Louwers Initial Creation
*
*******************************************************************/
(p_user_name IN VARCHAR2)
RETURN CHAR
IS
/* ------------------------------------------------------------------------------------*/
/* Select the profile option GUEST_USER_PWD, the value of the profile option is used as
a decryption key for the guest user encrypted password. */
CURSOR c_guest_profile
IS
SELECT
fnd_profile.value('GUEST_USER_PWD') AS PROFILE_OPTION
FROM
dual;
r_guest_profile c_guest_profile%ROWTYPE;
/* ------------------------------------------------------------------------------------*/
/* ------------------------------------------------------------------------------------*/
/*Select the encrypted guest user password so it can be decrypted in a later stage. The
decrypted guest user password is used as a decryption key for the user password.*/
CURSOR c_guest_user_password
IS
SELECT
usertable.encrypted_foundation_password
FROM
fnd_user usertable
WHERE
usertable.user_name LIKE upper('IBEGUEST');
r_guest_user_password c_guest_user_password%ROWTYPE;
/* ------------------------------------------------------------------------------------*/
/* ------------------------------------------------------------------------------------*/
/* Decrypt the guest user password using the profile option value as a decryption key. The
decrypted guest user password is used as a decryption key for the user password. */
CURSOR c_guest_password_decrypt
IS
SELECT
fnd_web_sec.decrypt(r_guest_profile.profile_option , r_guest_user_password.encrypted_foundation_password) AS GUEST_PWD
FROM
DUAL;
r_guest_password_decrypt c_guest_password_decrypt%ROWTYPE;
/* ------------------------------------------------------------------------------------*/
/* ------------------------------------------------------------------------------------*/
/* Select the encrypted user password from the user which you want to decrypt the password
from. The decryption of the user password is done in a later stage. */
CURSOR c_encrypted_password(
p_user_name VARCHAR2
)
IS
SELECT usertable.encrypted_user_password FROM fnd_user usertable WHERE USER_NAME = p_user_name;
r_encrypted_password c_encrypted_password%ROWTYPE;
/* ------------------------------------------------------------------------------------*/
/* ------------------------------------------------------------------------------------*/
/* Decrypt the password from the user using the decrypted guest user password as the decryption
key and the encrypted user password to be decrypted */
CURSOR c_decrypt_password(
key VARCHAR2
,password VARCHAR2
)
IS
SELECT fnd_web_sec.decrypt(key, password) AS DECRYPTED_PASSWORD from dual;
r_decrypt_password c_decrypt_password%ROWTYPE;
/* ------------------------------------------------------------------------------------*/
BEGIN
/*Open, fetch and close cursor responsible for the guest user profile option. */
OPEN c_guest_profile;
FETCH c_guest_profile INTO r_guest_profile;
CLOSE c_guest_profile;
/*Open, fetch and close cursor responsible for selecting the encrypted guest user password. */
OPEN c_guest_user_password;
FETCH c_guest_user_password INTO r_guest_user_password;
CLOSE c_guest_user_password;
/*Open, fetch and close cursor responsible for decrypting the guest user password. */
OPEN c_guest_password_decrypt;
FETCH c_guest_password_decrypt INTO r_guest_password_decrypt;
CLOSE c_guest_password_decrypt;
/*Open, fetch and close cursor responsible for selecting the encrypted user password. */
OPEN c_encrypted_password(p_user_name);
FETCH c_encrypted_password INTO r_encrypted_password;
CLOSE c_encrypted_password;
/*Open, fetch and close cursor responsible for decrypting the user password */
OPEN c_decrypt_password(r_guest_password_decrypt.guest_pwd, r_encrypted_password.encrypted_user_password);
FETCH c_decrypt_password INTO r_decrypt_password;
CLOSE c_decrypt_password;
/*Return the decrypted user password */
RETURN (r_decrypt_password.decrypted_password);
/*End the function */
END HH_TERM_DECRYPT;
cheers!!
Disclaimer: All The Contents are for educational purpose.Author is not responsible for any mishandling of the code causing legal issue.
/********************************************************************
* FILE NAME
* HH_TERM_DECRYPT.sql
*
*
* DESCRIPTION
* This Oracle function will decrypt the Oracle Apps password from
* a given user. The function can be called from a SQL command line
* by issuing the following command:
*
* SELECT HH_term_decrypt('HHHHHH') FROM DUAL;
*
* Where HHHHHH is the username of which the password is required.
* For this function to be able to work correctly a modification to
* the package specification of FND_WEB_SEC is needed. Please add
* the following line to the FND_WEB_SEC package:
*
* function decrypt(key in varchar2, value in varchar2) return varchar2;
*
* A more detailed description of this Oracle Password exploit can be
* found at the weblog of Johan Louwers: http://johanlouwers.blogspot.com/
* More information about Oracle security can be found at his website
* at http://www.terminalcult.org/
*
*
*
*
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* http://www.gnu.org/licenses/gpl.txt
*
*
* DOWNLOAD / CONTACT
* you can download the script from the following location:
* http://www.terminalcult.org/source/oracle/sql/HH_TERM_DECRYPT.sql
*
* You can contact Johan Louwers at Johan.Louwers(at)terminalcult.org
*
*
* HISTORY
* Version Date Author(s) Description
* ------- ----------- ------------------- --------------------------
* 1.0 02-JAN-2007 Johan Louwers Initial Creation
*
*******************************************************************/
(p_user_name IN VARCHAR2)
RETURN CHAR
IS
/* ------------------------------------------------------------------------------------*/
/* Select the profile option GUEST_USER_PWD, the value of the profile option is used as
a decryption key for the guest user encrypted password. */
CURSOR c_guest_profile
IS
SELECT
fnd_profile.value('GUEST_USER_PWD') AS PROFILE_OPTION
FROM
dual;
r_guest_profile c_guest_profile%ROWTYPE;
/* ------------------------------------------------------------------------------------*/
/* ------------------------------------------------------------------------------------*/
/*Select the encrypted guest user password so it can be decrypted in a later stage. The
decrypted guest user password is used as a decryption key for the user password.*/
CURSOR c_guest_user_password
IS
SELECT
usertable.encrypted_foundation_password
FROM
fnd_user usertable
WHERE
usertable.user_name LIKE upper('IBEGUEST');
r_guest_user_password c_guest_user_password%ROWTYPE;
/* ------------------------------------------------------------------------------------*/
/* ------------------------------------------------------------------------------------*/
/* Decrypt the guest user password using the profile option value as a decryption key. The
decrypted guest user password is used as a decryption key for the user password. */
CURSOR c_guest_password_decrypt
IS
SELECT
fnd_web_sec.decrypt(r_guest_profile.profile_option , r_guest_user_password.encrypted_foundation_password) AS GUEST_PWD
FROM
DUAL;
r_guest_password_decrypt c_guest_password_decrypt%ROWTYPE;
/* ------------------------------------------------------------------------------------*/
/* ------------------------------------------------------------------------------------*/
/* Select the encrypted user password from the user which you want to decrypt the password
from. The decryption of the user password is done in a later stage. */
CURSOR c_encrypted_password(
p_user_name VARCHAR2
)
IS
SELECT usertable.encrypted_user_password FROM fnd_user usertable WHERE USER_NAME = p_user_name;
r_encrypted_password c_encrypted_password%ROWTYPE;
/* ------------------------------------------------------------------------------------*/
/* ------------------------------------------------------------------------------------*/
/* Decrypt the password from the user using the decrypted guest user password as the decryption
key and the encrypted user password to be decrypted */
CURSOR c_decrypt_password(
key VARCHAR2
,password VARCHAR2
)
IS
SELECT fnd_web_sec.decrypt(key, password) AS DECRYPTED_PASSWORD from dual;
r_decrypt_password c_decrypt_password%ROWTYPE;
/* ------------------------------------------------------------------------------------*/
BEGIN
/*Open, fetch and close cursor responsible for the guest user profile option. */
OPEN c_guest_profile;
FETCH c_guest_profile INTO r_guest_profile;
CLOSE c_guest_profile;
/*Open, fetch and close cursor responsible for selecting the encrypted guest user password. */
OPEN c_guest_user_password;
FETCH c_guest_user_password INTO r_guest_user_password;
CLOSE c_guest_user_password;
/*Open, fetch and close cursor responsible for decrypting the guest user password. */
OPEN c_guest_password_decrypt;
FETCH c_guest_password_decrypt INTO r_guest_password_decrypt;
CLOSE c_guest_password_decrypt;
/*Open, fetch and close cursor responsible for selecting the encrypted user password. */
OPEN c_encrypted_password(p_user_name);
FETCH c_encrypted_password INTO r_encrypted_password;
CLOSE c_encrypted_password;
/*Open, fetch and close cursor responsible for decrypting the user password */
OPEN c_decrypt_password(r_guest_password_decrypt.guest_pwd, r_encrypted_password.encrypted_user_password);
FETCH c_decrypt_password INTO r_decrypt_password;
CLOSE c_decrypt_password;
/*Return the decrypted user password */
RETURN (r_decrypt_password.decrypted_password);
/*End the function */
END HH_TERM_DECRYPT;
cheers!!
Disclaimer: All The Contents are for educational purpose.Author is not responsible for any mishandling of the code causing legal issue.
Sunday, September 6, 2009
GL interface in oracle apps
GL Interface
Procedure:
1) We populate the Staging Table
2) Performed Validation on Staging Table.
3) Populate GL Interface
4) Used Concurrent Program : Journal IMPORT.
5) Report Used is : Journal Import Execution Report – That gives Info on failed import.
If we get few errors, then we fix the errors in GL_Interface.
7) If we get many Errors: Then we delete IMPORT Journals using
Concurrent Procss : Delete Journal Import Data.
- we clean the GL_Interface
- Fix the data from the Source and start over.
8) Post Journal Entries after successful Import.
Interface Tables: GL_INTERFACE
Base Tables : GL_JE_BACTHES,
GL_JE_HEADERS,
GL_JE_LINES.
[ GL_JE_Set_Of_Books, Gl_Code_Combinations, GL_JE_Source_TL, GL_JE_Categoies_TL, GL_Periods, fnd_Currencies ]
Staging Table Cols : AccountingDate, DateCreated, ActualFlag, CategoryName, SourceName,
Entered Debit/Credit, Segments 1-5, References 21-27,CCID, ProcessFlag, ErrorMsg.
(New, Yes ,Error)
Interface Cols : Status, SetOfBooksID, AccountingDate, CurrencyCode, DateCreated, CreatedBy, ActualFlag, UserJECategorName, UserJESourcename, Entered Debit/Credit, Segments 1-5, References 21-27.
Concurrent Program: Journal Import
Journal Posting --- Populates GL_BALANCES
Validations:-
1) JeHeaderID -- Unique
2) JeBatchID -- Unique
3) JeLineNum -- Uniqueness of HeaderID.
-- The Amount of the lines should match the total Amt of Header.
4) SetOfBooksID -- It has to exist to the set of Books Table.
5) JESourceName GL_JE_Source_TL -- Unique
6)JECategoryName -- Unique
7) CurrencyCode -- It has to be defined
8) PeriodSetName + Period_Name -- Should be open and defined.
9) CodeCombinationId Gl_Code_Combinations -- Should exist in Chart of Accounts.
Validations Type:
1) Batch Level: a) SOB b) BatchName c) PeriodName
This is done to ensure that batch doesn’t exist already.
2) Journal Level: Journal Entry Name, Currency Code, Accouting Date
3) Accounting Validations / Journal Entry Line level Validation
Code Combination ID: 1) Should be enabled in Accounting Date.
Validations for the staging table:
Check if the inputted data file is already uploaded into staging table.
Check if the record already exists in the interface table.
Check if the journal already exists in the GL application.
Procedure:
1) We populate the Staging Table
2) Performed Validation on Staging Table.
3) Populate GL Interface
4) Used Concurrent Program : Journal IMPORT.
5) Report Used is : Journal Import Execution Report – That gives Info on failed import.
If we get few errors, then we fix the errors in GL_Interface.
7) If we get many Errors: Then we delete IMPORT Journals using
Concurrent Procss : Delete Journal Import Data.
- we clean the GL_Interface
- Fix the data from the Source and start over.
8) Post Journal Entries after successful Import.
Interface Tables: GL_INTERFACE
Base Tables : GL_JE_BACTHES,
GL_JE_HEADERS,
GL_JE_LINES.
[ GL_JE_Set_Of_Books, Gl_Code_Combinations, GL_JE_Source_TL, GL_JE_Categoies_TL, GL_Periods, fnd_Currencies ]
Staging Table Cols : AccountingDate, DateCreated, ActualFlag, CategoryName, SourceName,
Entered Debit/Credit, Segments 1-5, References 21-27,CCID, ProcessFlag, ErrorMsg.
(New, Yes ,Error)
Interface Cols : Status, SetOfBooksID, AccountingDate, CurrencyCode, DateCreated, CreatedBy, ActualFlag, UserJECategorName, UserJESourcename, Entered Debit/Credit, Segments 1-5, References 21-27.
Concurrent Program: Journal Import
Journal Posting --- Populates GL_BALANCES
Validations:-
1) JeHeaderID -- Unique
2) JeBatchID -- Unique
3) JeLineNum -- Uniqueness of HeaderID.
-- The Amount of the lines should match the total Amt of Header.
4) SetOfBooksID -- It has to exist to the set of Books Table.
5) JESourceName GL_JE_Source_TL -- Unique
6)JECategoryName -- Unique
7) CurrencyCode -- It has to be defined
8) PeriodSetName + Period_Name -- Should be open and defined.
9) CodeCombinationId Gl_Code_Combinations -- Should exist in Chart of Accounts.
Validations Type:
1) Batch Level: a) SOB b) BatchName c) PeriodName
This is done to ensure that batch doesn’t exist already.
2) Journal Level: Journal Entry Name, Currency Code, Accouting Date
3) Accounting Validations / Journal Entry Line level Validation
Code Combination ID: 1) Should be enabled in Accounting Date.
Validations for the staging table:
Check if the inputted data file is already uploaded into staging table.
Check if the record already exists in the interface table.
Check if the journal already exists in the GL application.
AR Auto LockBox in oracle apps
AR Auto LockBox
Definition: AutoLockbox is a service that commercial bank offers to corporate customers for
outsourcing their Accounts Receivables payment processing. We can use
AutoLockBox for historical data conversion. We can only load Cash Receipts, not
miscellaneous receipts as there is no invoice & customers.
Set Ups done before using Autolockbox Prog:
!--1) Set Up Receipt Bank : We define Bank with ‘Account Use’: Internal where checks from
customers are deposited. [ ARàSetUpàReceiptsàBank ]
2) Set Up Receipt Class : Here we assign Payment methods. [SetUpàReceiptsàClass]
!--3) Set Up Receipt Source: We define Receipt Batch Source and assign Receipt Class, payment
method and Bank Account to this source. [Set upàReceipts àReceipt Source ]
!--4) Define LockBox : Define Lockbox to use the Receivable AutoLockbox Program.
[Set UpàReceipt à LockBoxes àLockbox ]
!--5) Define Transmission Format : Autolockbox uses Transmission format for importing data
into receivables. Here we define Lockbox Header, Payment,
Lockbox Trailer.
[ Setup àReceiptàLockboxàTransmission Format ]
[ Define LockBox tells how Lockbox will handle Invalid Transaction Number.
1. Post Partial Amount as Unapplied: Apply the receipt to the valid transactions, then import the remaining receipt amount with a status of Unapplied.
2. Reject Entire Receipt: It doesn’t import the invalid receipt and data will remain in Interface table [ Ar_Payments_Interface ]. We can edit the invalid records in the “Lockbox Transmission Data Window”, then resubmit the validation step for the receipts before Lockbox can import it into Receivables.
]
Process:
!--1) We create Control File for flat data file, we get from bank.
!--2) Move the .dat file and .ctl file to $AR_TOP/bin
!--3) After that we first do Import, then validation and finally Post QuickCash.
AutoLockBox is a three step Process:-
1. IMPORT – During this step, Lockbox reads and formats the data from our bank file into
Interface table > using a SQL*Loader script.
2. Validation– This checks data in the interface table for compatibility with Receivables. Once the
data is validated , the data is transferred into QuickCash tables [Receipt Tables]
(AR_INTERIM_CASH_RECEIPTS_ALL, AR_INTERIM_CASH_RCPT_LINES_ALL).
3. Post Quick Cash - It applies the receipts and update our customer’s balances, So data goes
to Base tables.
Interface tables: AR_PAYMENTS_INTERFACE_ALL
AR_INTERIM_CASH_RECEIPTS_ALL
AR_INTERIM_CASH_RCPT_LINES_ALL
Base Tables: AR_CASH_RECEIPTS_ALL,
AR_CASH_RECEIPTS_HISTORY_ALL,
AP_RECEIVABLES_APPLICATION_ALL
Interface table Cols:
For Header: Status, Record Type, LockBox#, Deposit Date, Origination.
For Payment: Status, Record Type, Customer#, Invoice1, Check#, Remittance Amt, Receipt
Date, Item Number, LockBox Number.
For Trailer: Status, RecordType, LockBox#, Deposit Date, BatchRecordCount, BatchAmt,
Origination.
Validations:
(A) [ TransmissionRecordID – Pk ]
Base Tables
Interface Cols
Validations
Record Type
Is NN. Type of Record should exist.(Header(HE) / Payment (DE)/ Trailer (TR))
LockBox Number
Should Exist.
AR_Batches
Deposit Date
Should be there. Entered by user using “Maintain Lockbox Transmission Date”.
AR Transmissions (Origin)
Origination
do
AP Bank Branches
(Bank Name, Bank Branch Name, Bank #)
Trans Routing#
-- do --
AR Cash Receipt
Receipt Date
-- do --
(B) [Cash Receipt ID – Pk]
Base Tables
Validations
Cash receipt ID
NN
Exists
Amount
NN
Should be there.
Currency Code
NN
Should exist in Fnd_Currencies.
GL Date
NN
Should be open.
Receipt Method ID
NN
Payment Method shod be specified.
Remittance Bank Account ID
NN
Shod have Bank Account, Assigned to receipt.
Who’s Columns
NN
Customer Trx ID
N
Should be there
Receipt Number
N
Receipt# - with Cash receipt should be there.
(C) -[Cash_Receipt_ID, Cash_Receipt_Line_ID-Pk]
Base Tables
Validations
Cash Receipt ID
NN
Should exist. Cash Receipt associated with Line.
Cash receipt Line ID
NN
Identifier of the individual, Cash_Receipt_Lines_All
Payment Sequence ID
NN
Should be there
Who’s Column
NN
Should be there
Sold to Customer
N
Identified of the customer. Associated with the Interim_Cash_Receipt_Line.
Customer Trx ID
N
Should be there
Definition: AutoLockbox is a service that commercial bank offers to corporate customers for
outsourcing their Accounts Receivables payment processing. We can use
AutoLockBox for historical data conversion. We can only load Cash Receipts, not
miscellaneous receipts as there is no invoice & customers.
Set Ups done before using Autolockbox Prog:
!--1) Set Up Receipt Bank : We define Bank with ‘Account Use’: Internal where checks from
customers are deposited. [ ARàSetUpàReceiptsàBank ]
2) Set Up Receipt Class : Here we assign Payment methods. [SetUpàReceiptsàClass]
!--3) Set Up Receipt Source: We define Receipt Batch Source and assign Receipt Class, payment
method and Bank Account to this source. [Set upàReceipts àReceipt Source ]
!--4) Define LockBox : Define Lockbox to use the Receivable AutoLockbox Program.
[Set UpàReceipt à LockBoxes àLockbox ]
!--5) Define Transmission Format : Autolockbox uses Transmission format for importing data
into receivables. Here we define Lockbox Header, Payment,
Lockbox Trailer.
[ Setup àReceiptàLockboxàTransmission Format ]
[ Define LockBox tells how Lockbox will handle Invalid Transaction Number.
1. Post Partial Amount as Unapplied: Apply the receipt to the valid transactions, then import the remaining receipt amount with a status of Unapplied.
2. Reject Entire Receipt: It doesn’t import the invalid receipt and data will remain in Interface table [ Ar_Payments_Interface ]. We can edit the invalid records in the “Lockbox Transmission Data Window”, then resubmit the validation step for the receipts before Lockbox can import it into Receivables.
]
Process:
!--1) We create Control File for flat data file, we get from bank.
!--2) Move the .dat file and .ctl file to $AR_TOP/bin
!--3) After that we first do Import, then validation and finally Post QuickCash.
AutoLockBox is a three step Process:-
1. IMPORT – During this step, Lockbox reads and formats the data from our bank file into
Interface table > using a SQL*Loader script.
2. Validation– This checks data in the interface table for compatibility with Receivables. Once the
data is validated , the data is transferred into QuickCash tables [Receipt Tables]
(AR_INTERIM_CASH_RECEIPTS_ALL, AR_INTERIM_CASH_RCPT_LINES_ALL).
3. Post Quick Cash - It applies the receipts and update our customer’s balances, So data goes
to Base tables.
Interface tables: AR_PAYMENTS_INTERFACE_ALL
AR_INTERIM_CASH_RECEIPTS_ALL
AR_INTERIM_CASH_RCPT_LINES_ALL
Base Tables: AR_CASH_RECEIPTS_ALL,
AR_CASH_RECEIPTS_HISTORY_ALL,
AP_RECEIVABLES_APPLICATION_ALL
Interface table Cols:
For Header: Status, Record Type, LockBox#, Deposit Date, Origination.
For Payment: Status, Record Type, Customer#, Invoice1, Check#, Remittance Amt, Receipt
Date, Item Number, LockBox Number.
For Trailer: Status, RecordType, LockBox#, Deposit Date, BatchRecordCount, BatchAmt,
Origination.
Validations:
(A) [ TransmissionRecordID – Pk ]
Base Tables
Interface Cols
Validations
Record Type
Is NN. Type of Record should exist.(Header(HE) / Payment (DE)/ Trailer (TR))
LockBox Number
Should Exist.
AR_Batches
Deposit Date
Should be there. Entered by user using “Maintain Lockbox Transmission Date”.
AR Transmissions (Origin)
Origination
do
AP Bank Branches
(Bank Name, Bank Branch Name, Bank #)
Trans Routing#
-- do --
AR Cash Receipt
Receipt Date
-- do --
(B) [Cash Receipt ID – Pk]
Base Tables
Validations
Cash receipt ID
NN
Exists
Amount
NN
Should be there.
Currency Code
NN
Should exist in Fnd_Currencies.
GL Date
NN
Should be open.
Receipt Method ID
NN
Payment Method shod be specified.
Remittance Bank Account ID
NN
Shod have Bank Account, Assigned to receipt.
Who’s Columns
NN
Customer Trx ID
N
Should be there
Receipt Number
N
Receipt# - with Cash receipt should be there.
(C) -[Cash_Receipt_ID, Cash_Receipt_Line_ID-Pk]
Base Tables
Validations
Cash Receipt ID
NN
Should exist. Cash Receipt associated with Line.
Cash receipt Line ID
NN
Identifier of the individual, Cash_Receipt_Lines_All
Payment Sequence ID
NN
Should be there
Who’s Column
NN
Should be there
Sold to Customer
N
Identified of the customer. Associated with the Interim_Cash_Receipt_Line.
Customer Trx ID
N
Should be there
AR Auto Invoice Interface in oracle apps
AR Auto Invoice Interface SetUp Needed : 1) Define Transaction [i.e Invoice ] Source : [ Set Up Transaction Sources ]2) Define Transaction Flexfield [i.e DFF] : [ Set Up FinancialDescriptiveSegments Process:1) Created Staging Table and then its Synonym in Apps schema.2) Using SQL*Loader, populate staging table.3) Run the AutoInvoice Interface (i.e pakage we created ) to populate Interface tables.4) Run the Concurrent Prog : AutoInvoice Master Program’ for importing to Base Tables.5) If we get any error, we can use ‘AutoInvoice Correct Form’ to fix the errors. Interface tables: RA_INTERFACE_LINES_ALL,RA_INTERFACE_DISTRIBUTIONS_ALLRA_INTERFACE_SALESREPS_ALL Base tables: RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALLRA_CUST_TRX_GL_DIST_ALL RA_CUSTOMER_TRX_LINE_SALESREPS Staging Table Col: Customer#, CustomerName, ItemDesc, Ref#, Amt, TrxDate, TrxType, Line#, StatusMsg.Interface Table Cols: BatchSourceName, SOBID, LineType, Desc, CurrencyCode, Amt, CustTrxTypename, TermName, OrigSystemBillCustomerRef, OrigSystemBillAddressRef, ConversionType,ConversionRate, TrxDate, GLDate, Qty, OrgID, InterfaceLineAttribute 1-4, InterfaceLineContext.InterfaceLineAttribute 1-4, Account Class, Org ID, Amount, CCID. Concurrent Program: Auto Invoice Master Program Validations: Check for amount, batch source name, conversion rate, conversion type.Validate orig_system_bill_customer_id, orig_system_bill_address_id, validate quantity, Validate if the amount includes tax flag. (A) Base TableInterface ColumnsValidationsBatch Source IDMust exist in RABatchSourcesAll (Name). BatchSourceType = ‘Foreign’.Batch Source NameSOB ID, Line Type, Desc, Currency Code, Conversion Type, Trx Date, Receipt Method Name, Interface StatusAll are Not Null Columns.(Exchange Rate)Conversion RateIf conversion type = ‘user’ then this column must not be null, otherwise it must be null.Exchange Rate TypeConversion TypeMust exist in (Revenue Amount)AmountIf LineType = ‘Charges’ Then this col must be NULL.(Extended Amount)AmountWhen Create_Clearing=’No’ then AutoInvoice will correct the Revenue Amounts that have the wrong currency precision.When Create_Clearing=’No’ thenIt will go to Revenue Amount Accounting Rule DurationAccounting Rule ID (B) : If in Auto-Invoice, we choose Auto-Accounting, Then we don’t need this Interface.Base TableRA_Cust_Trx_Line_GL_dist_AllInterface ColsValidationsAccount ClassMust be either Rev, Freight, Tax, Rec, charges, UnBill, or Unearn.CCIDMust exist in .RA_Customer_Trx_Lines_AllInterface Line ContextIf we pass lines with GlobalContext, then we have to set this col to ‘Global Data Elements’PercentThe Sum of ll Accunting distribution percentages for a Trx must sum to 100 for an account class.Segment 1-6(If we have 6 Accounting Flexfield Segments)Valid combination of Accounting Flexfield segment value must exist in . Who’s Column :- Last Updated By, Llast Update Date, Created By, Creation Date are Nulls here.
customer api in oracle apps
customer API
(Every API has 3 out Parameters – Return_Status, Msg_Count, Msg_Data)
Algorithm Used in API is:
1We create a record variable of the desired type (Party /Organization)
2Then we Populate the record with information from source.
3Then Call the Procedure to create Party / Organization and pass the record to the procedure as a parameter so that procedure put the information of the record variable in the base table.
1. Set the organization id
Exec dbms_application_info.set_client_info(‘204’);
2. Create a party and an account
a) HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE
b) HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE
c) HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
HZ_CUST_ACCOUNT_V2PUB.Create_Cust_Account(…)à Cust_Account_ID, Account#, PartyID, Party#.
3. Create a physical location
a) HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
HZ_LOCATION_V2PUB.Create_Location(..) à Location_ID
4. Create a party site using party_id you get from step 2 and location_id from step 3.
a) HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE
HZ_PARTY_SITE_V2PUB.Create_Party_Site( Party_ID, Location_ID ) à
Party_Site_ID, Party_Site#
5. Create an account site using account_id you get from step 2 and party_site_id from step 4.
a) HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE
HZ_CUST_ACCOUNT_SITE_V2PUB.Create_Cust_Acct_Site(Cust_Acct_ID, Party_Site_ID)
àCust_Acct_Site_ID
6. Create an account site use using cust_acct_site_id you get from step 5 ans site_use_code = ‘BILL_TO’.
a) HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE
b) HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
HZ_CUST_ACCOUNT_SITE_V2PUB.Create_Cust_Site_Use(Cust_Acct_Site_ID) à
Site_Use_ID
Interface Table: TCA API.
Base table: HZ_PARTIES HZ_PARTY_SITES HZ_LOCATIONS
HZ_CUST_ACCOUNTS HZ_CUST_SITE_USES_ALL
HZ_CUST_ACCT_SITES_ALL HZ_PARTY_SITE_USES
Validations: Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation: validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.
(Every API has 3 out Parameters – Return_Status, Msg_Count, Msg_Data)
Algorithm Used in API is:
1We create a record variable of the desired type (Party /Organization)
2Then we Populate the record with information from source.
3Then Call the Procedure to create Party / Organization and pass the record to the procedure as a parameter so that procedure put the information of the record variable in the base table.
1. Set the organization id
Exec dbms_application_info.set_client_info(‘204’);
2. Create a party and an account
a) HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE
b) HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE
c) HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
HZ_CUST_ACCOUNT_V2PUB.Create_Cust_Account(…)à Cust_Account_ID, Account#, PartyID, Party#.
3. Create a physical location
a) HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
HZ_LOCATION_V2PUB.Create_Location(..) à Location_ID
4. Create a party site using party_id you get from step 2 and location_id from step 3.
a) HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE
HZ_PARTY_SITE_V2PUB.Create_Party_Site( Party_ID, Location_ID ) à
Party_Site_ID, Party_Site#
5. Create an account site using account_id you get from step 2 and party_site_id from step 4.
a) HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE
HZ_CUST_ACCOUNT_SITE_V2PUB.Create_Cust_Acct_Site(Cust_Acct_ID, Party_Site_ID)
àCust_Acct_Site_ID
6. Create an account site use using cust_acct_site_id you get from step 5 ans site_use_code = ‘BILL_TO’.
a) HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE
b) HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
HZ_CUST_ACCOUNT_SITE_V2PUB.Create_Cust_Site_Use(Cust_Acct_Site_ID) à
Site_Use_ID
Interface Table: TCA API.
Base table: HZ_PARTIES HZ_PARTY_SITES HZ_LOCATIONS
HZ_CUST_ACCOUNTS HZ_CUST_SITE_USES_ALL
HZ_CUST_ACCT_SITES_ALL HZ_PARTY_SITE_USES
Validations: Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation: validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.
Thursday, September 3, 2009
AR receipt application in R12
The following api apply the receipt created.
The minimum parameters required are given below.
AR_RECEIPT_API_PUB.Apply(
-- Standard API parameters.
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => L_RETURN_STATUS1,
x_msg_count => L_MSG_COUNT1,
x_msg_data => L_MSG_DATA1,
-- Receipt application parameters.
p_cash_receipt_id => l_RECEIPT_ID,
p_receipt_number => NULL,
p_customer_trx_id => l_customer_trx_id,
p_trx_number => NULL,
p_installment => NULL,
p_applied_payment_schedule_id => NULL,
p_amount_applied => l_receipt_amt,
-- this is the allocated receipt amount
p_amount_applied_from => NULL,
p_trans_to_receipt_rate => NULL,
p_discount => NULL,
p_apply_date => SYSDATE,
p_apply_gl_date => SYSDATE,
p_ussgl_transaction_code => NULL,
p_customer_trx_line_id => NULL,
p_line_number => NULL,
p_show_closed_invoices => NULL,
p_called_from => NULL,
p_move_deferred_tax => NULL,
p_link_to_trx_hist_id => NULL,
p_attribute_rec => AR_RECEIPT_API_PUB.ATTRIBUTE_REC_CONST,
-- ******* Global Flexfield parameters *******
p_global_attribute_rec => AR_RECEIPT_API_PUB.GLOBAL_ATTRIBUTE_REC_CONST,
p_comments => L_ORDER_NUMBER,
p_payment_set_id => NULL,
p_application_ref_type => l_application_ref_type,
p_application_ref_id => l_application_ref_id,
p_application_ref_num => l_application_ref_num,
p_secondary_application_ref_id => l_secondary_application_ref_id,
p_application_ref_reason => NULL,
p_customer_reference => NULL,
p_customer_reason => NULL,
p_org_id => fnd_profile.VALUE('ORG_ID')
);
The minimum parameters required are given below.
AR_RECEIPT_API_PUB.Apply(
-- Standard API parameters.
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => L_RETURN_STATUS1,
x_msg_count => L_MSG_COUNT1,
x_msg_data => L_MSG_DATA1,
-- Receipt application parameters.
p_cash_receipt_id => l_RECEIPT_ID,
p_receipt_number => NULL,
p_customer_trx_id => l_customer_trx_id,
p_trx_number => NULL,
p_installment => NULL,
p_applied_payment_schedule_id => NULL,
p_amount_applied => l_receipt_amt,
-- this is the allocated receipt amount
p_amount_applied_from => NULL,
p_trans_to_receipt_rate => NULL,
p_discount => NULL,
p_apply_date => SYSDATE,
p_apply_gl_date => SYSDATE,
p_ussgl_transaction_code => NULL,
p_customer_trx_line_id => NULL,
p_line_number => NULL,
p_show_closed_invoices => NULL,
p_called_from => NULL,
p_move_deferred_tax => NULL,
p_link_to_trx_hist_id => NULL,
p_attribute_rec => AR_RECEIPT_API_PUB.ATTRIBUTE_REC_CONST,
-- ******* Global Flexfield parameters *******
p_global_attribute_rec => AR_RECEIPT_API_PUB.GLOBAL_ATTRIBUTE_REC_CONST,
p_comments => L_ORDER_NUMBER,
p_payment_set_id => NULL,
p_application_ref_type => l_application_ref_type,
p_application_ref_id => l_application_ref_id,
p_application_ref_num => l_application_ref_num,
p_secondary_application_ref_id => l_secondary_application_ref_id,
p_application_ref_reason => NULL,
p_customer_reference => NULL,
p_customer_reason => NULL,
p_org_id => fnd_profile.VALUE('ORG_ID')
);
Wednesday, September 2, 2009
Submitting concurrent Program via API
For demo only.
DECLARE
V_REQUEST_ID NUMBER;
FILEPATH VARCHAR2(250);
V_DEV_PHASE VARCHAR2(250);
V_WAIT BOOLEAN;
V_PHASE VARCHAR2(250);
V_STATUS VARCHAR2(250);
V_DEV_STATUS VARCHAR2(250);
V_MESSAGE VARCHAR2(250);
CSTATUS_CODE VARCHAR2(250);
BEGIN
V_DEV_PHASE:='R';
V_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST('APPLICATION SHORT NAME','PROGRAM SHORT NAME',NULL,NULL,FALSE,PARAMETER1); -- submit request
WHILE V_DEV_PHASE != 'COMPLETE' -- wait until the program is completed
LOOP
V_WAIT:= FND_CONCURRENT.WAIT_FOR_REQUEST(V_REQUEST_ID
,15
,10
,V_PHASE
,V_STATUS
,V_DEV_PHASE
,V_DEV_STATUS
,V_MESSAGE);
DBMS_OUTPUT.PUT_LINE('DEV PHASE IS :'||V_DEV_PHASE);
EXIT WHEN V_DEV_PHASE = 'COMPLETE';
END LOOP;
SELECT DECODE --To find the staus of completed program
(STATUS_CODE,
'A','WAITING',
'B','RESUMING',
'C','NORMAL',
'D','CANCELLED',
'E','ERROR',
'F','SCHEDULED',
'G','WARNING',
'H','ON HOLD',
'I','NORMAL',
'M','NO MANAGER',
'Q','STANDBY',
'R','NORMAL',
'S','SUSPENDED',
'T','TERMINATING',
'U','DISABLED',
'W','PAUSED',
'X','TERMINATED',
'Z','WAITING',
STATUS_CODE) INTO CSTATUS_CODE FROM FND_CONCURRENT_REQUESTS WHERE REQUEST_ID=V_REQUEST_ID;
DBMS_OUTPUT.PUT_LINE(CSTATUS_CODE);
END;
DECLARE
V_REQUEST_ID NUMBER;
FILEPATH VARCHAR2(250);
V_DEV_PHASE VARCHAR2(250);
V_WAIT BOOLEAN;
V_PHASE VARCHAR2(250);
V_STATUS VARCHAR2(250);
V_DEV_STATUS VARCHAR2(250);
V_MESSAGE VARCHAR2(250);
CSTATUS_CODE VARCHAR2(250);
BEGIN
V_DEV_PHASE:='R';
V_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST('APPLICATION SHORT NAME','PROGRAM SHORT NAME',NULL,NULL,FALSE,PARAMETER1); -- submit request
WHILE V_DEV_PHASE != 'COMPLETE' -- wait until the program is completed
LOOP
V_WAIT:= FND_CONCURRENT.WAIT_FOR_REQUEST(V_REQUEST_ID
,15
,10
,V_PHASE
,V_STATUS
,V_DEV_PHASE
,V_DEV_STATUS
,V_MESSAGE);
DBMS_OUTPUT.PUT_LINE('DEV PHASE IS :'||V_DEV_PHASE);
EXIT WHEN V_DEV_PHASE = 'COMPLETE';
END LOOP;
SELECT DECODE --To find the staus of completed program
(STATUS_CODE,
'A','WAITING',
'B','RESUMING',
'C','NORMAL',
'D','CANCELLED',
'E','ERROR',
'F','SCHEDULED',
'G','WARNING',
'H','ON HOLD',
'I','NORMAL',
'M','NO MANAGER',
'Q','STANDBY',
'R','NORMAL',
'S','SUSPENDED',
'T','TERMINATING',
'U','DISABLED',
'W','PAUSED',
'X','TERMINATED',
'Z','WAITING',
STATUS_CODE) INTO CSTATUS_CODE FROM FND_CONCURRENT_REQUESTS WHERE REQUEST_ID=V_REQUEST_ID;
DBMS_OUTPUT.PUT_LINE(CSTATUS_CODE);
END;
Registering Tables In Oracl Apps
Introduction
Though Oracle Applications comes with thousands of seeded database tables, there can be numerous applications in which one might be required to create a custom table to be used. In most of the applications all you need is to create a table in a schema and use it directly in your applications. Flexfields and Oracle Alert are the only features or products that require the custom tables to be registered in Oracle Applications (Application Object Library) before they can be used.
You register your custom application tables using a PL/SQL procedure in the AD_DD package.
Therefore you only need to register those tables (and all of their columns) that will be used with flexfields or Oracle Alert.
You can also use the AD_DD API to delete the registrations of tables and columns from Oracle Application Object Library tables should you later modify your tables. If you alter the table later, then you may need to include revised or new calls to the table registration routines. To alter a registration you should first delete the registration, and then re-register the table or column. Remember, you should delete the column registration first, then the table registration. You should include calls to the table registration routines in a PL/SQL script. Though you create your tables in your own application schema, you should run the AD_DD procedures against the APPS schema. You must commit your changes for them to take effect.
The AD_DD API does not check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. You must ensure that the tables and columns registered actually exist and have the same format as that defined using the AD_DD API. You need not register views.
Procedures in the AD_DD Package
1. Procedure REGISTER_TABLE
procedure register_table ( p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_tab_type in varchar2,
p_next_extent in number default 512,
p_pct_free in number default 10,
p_pct_used in number default 70);
2. Procedure REGISTER_COLUMN
procedure register_column (p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2,
p_col_seq in number,
p_col_type in varchar2,
p_col_width in number,
p_nullable in varchar2,
p_translate in varchar2,
p_precision in number default null,
p_scale in number default null);
3. Procedure DELETE_TABLE
procedure delete_table (p_appl_short_name in varchar2,
p_tab_name in varchar2);
4. Procedure DELETE_COLUMN
procedure delete_column (p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2);
| VARIABLE NAMES
| DESCRIPTION
|
| p_appl_short_name
| The application short name of the application that owns the table (usually your custom application).
|
| p_tab_name
| The name of the table (in uppercase letters).
|
| p_tab_type
| Use ’T’ if it is a transaction table (almost all application tables), or ’S’ for a ”seed data” table (used only by Oracle Applications products).
|
| p_pct_free
| The percentage of space in each of the table’s blocks reserved for future updates to the table (1–99). The sum of p_pct_free and p_pct_used must be less than 100.
|
| p_pct_used
| Minimum percentage of used space in each data block of the table (1–99). The sum of p_pct_free and p_pct_used must be less than 100.
|
| p_col_name
| The name of the column (in uppercase letters).
|
| p_col_seq
| The sequence number of the column in the table (the order in which the column appears in the table definition).
|
| p_col_type
| The column type (’NUMBER’, ’VARCHAR2’, ’DATE’, etc.).
|
| p_col_width
| The column size (a number). Use 9 for DATE columns, 38 for NUMBER columns (unless it has a specific width).
|
| p_nullable
| Use ’N’ if the column is mandatory or ’Y’ if the column allows null values.
|
| p_translate
| Use ’Y’ if the column values will be translated for an Oracle Applications product release (used only by Oracle Applications products) or ’N’ if the
values are not translated (most application columns).
|
| p_next_extent
| The next extent size, in kilobytes. Do not include the ’K’.
|
| p_precision
| The total number of digits in a number.
|
| p_scale
| The number of digits to the right of the decimal point in a number.
|
Example of Using the AD_DD Package
Here is an example of using the AD_DD package to register a flexfield table and its columns:
Though the use of AD_DD package does not require that the table should exist first, it is always better to create one and proceed further. Use the below mentioned script to create the dummy table. Use the APPS User ID to run the below mentioned queries from TOAD or SQL*PLUS.
CREATE TABLE TEST_DESC ( RESOURCE_NAME VARCHAR2 (150),
RESOURCE_TYPE VARCHAR2 (100),
ATTRIBUTE_CATEGORY VARCHAR2 (40),
ATTRIBUTE1 VARCHAR2 (150),
ATTRIBUTE2 VARCHAR2 (150),
ATTRIBUTE3 VARCHAR2 (150),
ATTRIBUTE4 VARCHAR2 (150),
ATTRIBUTE5 VARCHAR2 (150),
ATTRIBUTE6 VARCHAR2 (150)
);
BEGIN
AD_DD.REGISTER_TABLE ('FND','TEST_DESC','T');
END;
BEGIN
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','RESOURCE_NAME', 1, 'VARCHAR2', 150, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','RESOURCE_TYPE', 2, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','ATTRIBUTE_CATEGORY', 3, 'VARCHAR2', 40, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','ATTRIBUTE1', 4, 'VARCHAR2', 150, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','ATTRIBUTE2', 5, 'VARCHAR2', 150, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','ATTRIBUTE3', 6, 'VARCHAR2', 150, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','ATTRIBUTE4', 7, 'VARCHAR2', 150, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','ATTRIBUTE5', 8, 'VARCHAR2', 150, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'TEST_DESC','ATTRIBUTE6', 9, 'VARCHAR2', 150, 'Y', 'N');
END;
Subscribe to:
Posts (Atom)