AP Invoice Interface

AP Invoice Interface:

select lookup_code

from ap_lookup_codes

where lookup_type='SOURCE'

INSERT

INTO ap_invoices_interface

(

invoice_id,

INVOICE_NUM,

DESCRIPTION,

invoice_type_lookup_code,

invoice_date,

vendor_id,

vendor_site_id,

invoice_amount,

INVOICE_CURRENCY_CODE,

source,

payment_method_code,

taxation_country

org_id

)

VALUES

(

AP_INVOICES_INTERFACE_S.NEXTVAL

, 'TESTINV01'

,'this is test invoice'

,'STANDARD'

,SYSDATE -40 --<invoice date>

,35028--<vendor_id validated against po_vendors.vendor_id>,

,2277--<vendor_site_id validated against po_vendor_sites.vendor_site_id>,

,100--<invoice_amount>

,'AED'

,'MANUAL INVOICE ENTRY'

,'CHECK'

, 'AE'

,81

);

INSERT

INTO ap_invoice_lines_interface

(

invoice_id,

invoice_line_id,

line_number,

line_type_lookup_code,

amount,

accounting_date,

dist_code_combination_id

)

VALUES

(

AP_INVOICES_INTERFACE_S.CURRVAL

,AP_INVOICE_LINES_INTERFACE_S.NEXTVAL

,1--<some unique_number>,

,'ITEM'--<ITEM>/<FREIGHT>/<TAX>

,100--<line_amount> sum of line_amount should always be equal to invoice_amount,

,SYSDATE -40

,4565 --'<account code>'—CC_ID

);

COMMIT;

After inserting, run the “PAYABLES OPEN INTERFACE IMPORT” program to load invoice

Purchase Requisition(PR) Creation Interface

Sample Script to create PR:

INSERT INTO po_requisitions_interface_all

                        (interface_source_code

                       , source_type_code

                      , destination_type_code, authorization_status

                      , preparer_id, category_id

                      , item_description, quantity

                      , unit_price, charge_account_id

                      , unit_of_measure

                      , suggested_vendor_id

                      , destination_organization_id

                      , deliver_to_location_id

                       , deliver_to_requestor_id

                      , org_id

                      , group_code,

                      )

                 VALUES (

                      'FSI'

                  , ‘VENDOR’

                  , ‘EXPENSE’

                  , 'INCOMPLETE'

                  , 200 --Person ID

                  , 1233  --Category ID

                  , ‘This is test item’  --Item Description

                   , 10 --Quantity

                   ,   100 --Unit Price

                   , 123  --CC_Id

                   , ’Each’

                   , 231 --suggested_vendor_id

                   , 101   --Destination Org ID

                    , 142 --Deliver to location ID

                     , 200 --Person ID

                     , 81 --Org ID

                     , 103 --Group Code --This is to group the requisition lines

                       );

Then run Requisition Import program:

Changing Oracle logo in Oracle Applications

Name of the Profile - Corporate Branding Image for Oracle Applications

Name of Image – Image_Name.gif (Provide Your Image Name)

Copy the Image to $OA_MEDIA directory.

Clear the Cache:

Navigation to clear the cache:

Responsibility -> Functional Administrator -> Core Services -> Caching Framework -> Global Configuration -> Clear All Cache -> Yes

Then Refresh the login page to see the new image

Table to get Environment Paths

FND_ENV_CONTEXT

XML Publisher in multiple Languages

Requirement:

Need to develop one template in which language (font) get changed
as we choose other country organization.

Solution:

1. Other language should be enabled on your oracle apps instance.
2. Create the concurrent program with output type as XML
3. Create Data Definition using XML Publisher Responsibility
4. Create template. At the time of creation of templete, enable Translatable check box and click Apply
5. Once template is uploaded, you will find two buttons. Export Translation and Upload Translation.
6. Click on Export Translation. XLF file will be opened.
7. In XLF file - e. g. source-language="en-US" target-language="fr-FR".Source is English and fr is for French language and FR is territory.
8. Update the target fields in French language. In the below example source field is in English language and target is in French language. This indicates that wherever the Invoice Date is present in RTF template it will be
replaced by the target field in french language.
<source>Invoice Date:</source>
<target>Date de facture:</target>
9. Update the same for all fields and finally import the updated translation file.

Matrix Report in XML Publisher

This report is number of JVs created per month per source wise.

Query:select je_source,to_char(date_created,'Mon-YY') Source_Month,count(je_header_id) je_count from gl_je_headerswhere to_char(date_created,'Q') = :p_quarter and to_char(date_created,'YYYY') = :p_year group
 by je_source,to_char(date_created,'Mon-YY')
RDF report:
    Total Number of JVs Quarterly

Source Name	GSource_MonthE	Total
GJE_SOURCE	G If exists: JE_COUNT end; E	999E
Total	G If exists: 999 end; E
O               G Close to JE_SOURCE - <?for-each-   group@section:G_SERVICE_NAME;./JE_SOURCE?><?sort:JE_SOURCE;'ascending';data-type='text'?>  JE_SOURCE - <?JE_SOURCE?><?variable@incontext:JS;JE_SOURCE?>         G close to Source_Month : <?for-each-group@column:G_SERVICE_NAME;./SOURCE_MONTH?> Source_Month - <?SOURCE_MONTH?> E  - <?end for-each-group?> G Near If Exists -  <?for-each-group@cell://G_SERVICE_NAME;./SOURCE_MONTH? IF Exists – <?if:count(current-group()[JE_SOURCE=$JS])?> JE_COUNT - <?current-group()[JE_SOURCE=$JS]/JE_COUNT?>  End; - <?end if?> E  - <?end for-each-group?> G in Total -  <?for-each-group@cell://G_SERVICE_NAME;./SOURCE_MONTH? IF Exists in Total – <?if:count(current-group())?> 999 in Total <?sum(current-group()/JE_COUNT)?> End if in Total: <?end if?> E in Total – <?end for-each-group?> 999 in Total Column - <?sum(//G_SERVICE_NAME[JE_SOURCE=$JS]/JE_COUNT)?> E in Total Column: <?end for-each-group?>

XML Publisher (BI Publisher) cell highlighting.

Color change in XML Publisher:

Requirement: If debit or credit value is greater than 1000 then it should become red.
XML File:<?xml version="1.0" encoding="WINDOWS-1252"?>
<!-- Generated by Oracle Reports version 10.1.2.0.2 -->
<XX_GROUP>
<accounts>
<account>
<number>1-100-3333</number>
<debit>100</debit>
<credit>300</credit>
</account>
<account>
<number>1-101-3533</number>
<debit>220</debit>
<credit>30</credit>
</account>
<account>
<number>1-130-3343</number>
<debit>240</debit>
<credit>1100</credit>
</account>
<account>
<number>1-153-3033</number>
<debit>3000</debit>
<credit>300</credit>
</account>
</accounts>
</XX_GROUP>
RTF Template:

Account	Debit	Credit
FE ACCOUNT	CH1100.00	CH2100.00 E

　The field definitions for the template are shown in the following table:

Default Text Entry	Form Field Entry	Description
FE	<?for-each:account?>	Opens the for each-loop for the element account.
ACCOUNT	<?number?>	The placeholder for the number element from the XML file.
CH1	<?if:debit>1000?><xsl:attribute xdofo:ctx="block" name="background-color">red</xsl:attribute><?end if?>	This field holds the code to highlight the cell red if the debit amount is greater than 1000.
100.00	<?debit?>	The placeholder for the debit element.
CH2	<?if:credit>1000?><xsl:attribute xdofo:ctx="block" name="background-color">red</xsl:attribute><?end if?>	This field holds the code to highlight the cell red if the credit amount is greater than 1000.
100.00	<?credit?>	The placeholder for the credit element.
EFE	<?end for-each?>	Closes the for-each loop.

The code to highlight the debit column as shown in the table is<?if:debit>1000?>
<xsl:attribute
xdofo:ctx="block" name="background-color">red
</xsl:attribute>
<?end if?>The "if" statement is testing if the debit value is greater than 1000. If it is, then the next lines are invoked.

That's it!!!