Usage of Merge Statement in Oracle 

The MERGE statement was introduced in Oracle 9i to conditionally insert or update data depending on its presence, a process also known as an "upsert". The MERGE statement reduces table scans and can perform the operation in parallel if required.

MERGE INTO po_distributions_all D
   USING (select y.po_header_id,x.expenditure_type, x.expenditure_org,x.expenditure_date
from xx_nic_po_headers x,
po_headers_all y
 where x.final_po_no = y.segment1
 and y.segment1 in
 ('H0010262')) S
   ON (D.po_header_id=s.po_header_id)
   WHEN MATCHED THEN UPDATE SET d.expenditure_type=s.expenditure_type,
   d.expenditure_item_date=s.expenditure_date,
   d.expenditure_organization_id=s.expenditure_org

Moving Personalizations from One Instance to Another Instance

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct $HOME/sud/fin/ldt/personalization/XX_NICSI_INVOICE_PER.ldt FND_FORM_CUSTOM_RULES function_name="AP_APXINWKB"


FNDLOAD apps/appsvis 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_NICSI_INVOICE_PER.ldt

Payables Open Interface Import

Required and optional columns of Payables open interface import of PO matched invoices (R12)

In this post, I tried to explain the required and optional columns in the payables invoice import tables for importing PO Matched Invoices. 

TABLE:  AP_INVOICE_INTERFACE

  Required Columns 

Column Name	Validation
INVOICE_ID	Populated from AP_INVOICES_INTERFACE_S.NEXTVAL
INVOICE_NUM	Must be unique to the supplier
PO_NUMBER	An approved, not cancelled, not closed or final closed PO
VENDOR_ID or VENDOR_NUM or VENDOR_NAME	An active vendor. Validated against PO_VENDORS
VENDOR_SITE_ID or VENDOR_SITE_CODE	An active pay site. Validated against PO_VENDOR_SITES
INVOICE_AMOUNT	Positive amount for 'STANDARD' type, Negative amount for 'CREDIT' type
ORG_ID	Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS.ORG_ID
SOURCE	Must be in SELECT lookup_code   FROM ap_lookup_codes  WHERE lookup_type = 'SOURCE';

     Optional Columns 

Column Name	Validation
INVOICE_DATE	Defaulted to SYSDATE
INVOICE_TYPE_LOOKUP_CODE	Defaulted to 'STANDARD'. It can be 'STANDARD' or 'CREDIT'
INVOICE_CURRENCY_CODE	Defaulted from PO_VENDOR_SITES. INVOICE_CURRENCY_CODE
EXCHANGE_RATE_TYPE	Defaulted from AP_SYSTEM_PARAMETERS. DEFAULT_EXCHANGE_RATE_TYPE
TERMS_ID or TERMS_NAME	Defaulted from  PO_VENDOR_SITES.TERMS_ID
DOC_CATEGORY_CODE	Only populated if using automatic voucher number
PAYMENT_METHOD_LOOKUP_CODE	Defaulted from PO_VENDOR_SITES .PAYMENT_METHOD_LOOKUP_CODE
PAY_GROUP_LOOKUP_CODE	Defaulted from PO_VENDOR_SITES. PAY_GROUP_LOOKUP_CODE
ACCTS_PAY_CODE_COMBINATION_ID	Defaulted from PO_VENDOR_SITES. ACCTS_PAY_CODE_COMBINAITON_ID
GROUP_ID	Group identifier. Suggest to use it
STATUS	DO NOT POPULATE IT

 Table:  AP_INVOICE_LINES_INTERFACE 
 Required Columns  

Column Name	Validation
INVOICE_ID	Populated from AP_INVOICES_INTERFACE.INVOICE_ID
INVOICE_LINE_ID	Populated from AP_INVOICE_LINES_INTERFACE_S. NEXTVAL
LINE_NUMBER	A unique number to the invoice
TAX_CODE or TAX_CODE_ID	Validated against AP_TAX_CODES_ALL
LINE_TYPE_LOOKUP_CODE	'ITEM'
AMOUNT	Should be QUANTITY_INVOICED * UNIT_PRICE
If MATCH_OPTION is 'P', then populate
RELEASE_NUM or PO_RELEASE_ID	For Blanket Release only,  validated against PO_RELEASES_ALL
PO_NUMBER or PO_HEADER_ID	Validated against PO_HEADER_ALL
PO_LINE_NUMBER or PO_LINE_ID	Validated against PO_LINES_ALL
PO_SHIPMENT_NUM or PO_LINE_LOCATION_ID	Validated against PO_LINE_LOCATIONS_ALL
If MATCH_OPTION is 'R', then populate
RECEIPT_NUMBER	Validated against RCV_SHIPMENT_HEADERS.RECEIPT_NUM
RCV_TRANSACTION_ID or PO_LINE_LOCATION_ID	Validated against RCV_TRANSACTIONS

Optional Columns

Column Name	Validation
QUANTITY_INVOICED	Populated if different from PO shipment
UNIT_PRICE	Populated if different from PO shipment
MATCH_OPTION	'P' or 'R' or Defaulted from PO_VENDOR_SITES.MATCH_OPTION
ACCOUNTING_DATE	Defaulted from INVOICE_DATE or SYSDATE
FINAL_MATCH_FLAG	Populated 'Y' if it is final matching
INVENTORY_ITEM_ID	Validated against PO_LINES.INVENTORY_ITEM_ID
INVENTORY_DESCRIPTION	Validated against PO_LINES.INVENTORY_ITEM_DESCRIPTION
SHIP_TO_LOCATION_CODE	Populated if different from PO shipment
PRICE_CORRECTION_FLAG	Populated 'Y' if it is price correction

=======

In this post, I tried to explain the required and optional columns in the payables invoice import tables for importing simple invoices i.e. not matched with any PO or Project Related.  Table:  AP_INVOICE_INTERFACE    Required Columns  Column Name Validation INVOICE_ID                             Populated from AP_INVOICES_INTERFACE_S.NEXTVAL INVOICE_NUM                            Must be unique to the supplier VENDOR_ID or VENDOR_NUM or VENDOR_NAME An active vendor. Validated against PO_VENDORS VENDOR_SITE_ID or VENDOR_SITE_CODE An active pay site. Validated against PO_VENDOR_SITES INVOICE_AMOUNT                         Positive amount for 'STANDARD' type, Negative amount for 'CREDIT' type ORG_ID                                 Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS.ORG_ID SOURCE                                 Must be in  SELECT lookup_code   FROM ap_lookup_codes  WHERE lookup_type = 'SOURCE'      Optional Columns      Column Name Validation INVOICE_DATE Defaulted to SYSDATE INVOICE_TYPE_LOOKUP_CODE               Defaulted to 'STANDARD'.  It can be 'STANDARD' or 'CREDIT' INVOICE_CURRENCY_CODE                  Defaulted from PO_VENDOR_SITES.INVOICE_CURRENCY_CODE EXCHANGE_RATE_TYPE                     Defaulted from AP_SYSTEM_PARAMETERS. DEFAULT_EXCHANGE_RATE_TYPE TERMS_ID or TERMS_NAME                 Defaulted from PO_VENDOR_SITES.TERMS_ID DOC_CATEGORY_CODE                       Only populated if using automatic voucher number PAYMENT_METHOD_LOOKUP_CODE Defaulted from PO_VENDOR_SITES. PAYMENT_METHOD_LOOKUP_CODE PAY_GROUP_LOOKUP_CODE Defaulted from PO_VENDOR_SITES.PAY_GROUP_LOOKUP_CODE ACCTS_PAY_CODE_COMBINATION_ID Defaulted from PO_VENDOR_SITES .ACCTS_PAY_CODE_COMBINAITON_ID GROUP_ID Group identifier. Suggest to use it STATUS DO NOT POPULATE IT  Table:  AP_INVOICE_LINES_INTERFACE   LINE_TYPE_LOOKUP_CODE = 'ITEM' Required Columns     Column Name Validation INVOICE_ID                             Populated from AP_INVOICES_INTERFACE.INVOICE_ID INVOICE_LINE_ID                        Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL LINE_NUMBER         A unique number to the invoice LINE_TYPE_LOOKUP_CODE                   'ITEM' AMOUNT                               ACCOUNTING_DATE Optional. Defaulted from INVOICE_DATE or SYSDATE DIST_CODE_CONCATENATED or DIST_CODE_COMBINATION_ID Validated against  - 'Parent' must be 'No' for All the segments values  - DIST_CODE_CONCATENATED needs to meet the security rules  - DIST_CODE_COMBINATION_ID must be in SELECT code_combination_id   FROM gl_code_combinations  WHERE account_type = 'E'    AND enabled_flag = 'Y'    AND summary_flag = 'N'    AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE - 1)                    AND NVL(end_date_active, SYSDATE + 1) LINE_TYPE_LOOKUP_CODE = 'TAX'  Required Columns     Column Name Validation INVOICE_ID                             Populated from AP_INVOICES_INTERFACE.INVOICE_ID INVOICE_LINE_ID                        Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL LINE_NUMBER         A unique number to the invoice TAX_CODE or TAX_CODE_ID Validated against AP_TAX_CODES_ALL LINE_TYPE_LOOKUP_CODE                  'TAX' AMOUNT                               ACCOUNTING_DATE Optional. Defaulted from INVOICE_DATE orSYSDATE DIST_CODE_CONCATENATED or DIST_CODE_COMBINATION_ID Optional.  Defaulted from AP_TAX_CODES.TAX_CODE_COMBINATION_ID. If one of them is populated, then validated against  - 'Parent' must be 'No' for All the segments values  - DIST_CODE_CONCATENATED needs to meet the security rules  - DIST_CODE_COMBINATION_ID must be in SELECT code_combination_id   FROM gl_code_combinations  WHERE account_type = 'E'    AND enabled_flag = 'Y'    AND summary_flag = 'N'    AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE - 1)                    AND NVL(end_date_active, SYSDATE + 1) LINE_TYPE_LOOKUP_CODE = 'FREIGHT' Required Columns     Column Name Validation INVOICE_ID                             Populated from AP_INVOICES_INTERFACE.INVOICE_ID INVOICE_LINE_ID                        Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL LINE_NUMBER         A unique number to the invoice LINE_TYPE_LOOKUP_CODE                   'FREIGHT' AMOUNT                               ACCOUNTING_DATE Optional. Defaulted from INVOICE_DATE or SYSDATE DIST_CODE_CONCATENATED or DIST_CODE_COMBINATION_ID Optional. Defaulted from AP_SYSTEM_PARAMETERS. FREIGHT_CODE_COMBINATION_ID. If one of them is populated, then validated against  - 'Parent' must be 'No' for All the segments values  - DIST_CODE_CONCATENATED needs to meet the security rules  - DIST_CODE_COMBINATION_ID must be in SELECT code_combination_id   FROM gl_code_combinations  WHERE account_type = 'E'    AND enabled_flag = 'Y'    AND summary_flag = 'N'    AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE - 1)                    AND NVL (end_date_active,SYSDATE + 1)

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

Base Tables:

                AP_INVOICES_ALL

                AP_INVOICE_LINES_ALL

                AP_INVOICE_DISTRIBUTIONS_ALL

                                     (&)

                Payment Tables

PAYABLES OPEN INTERFACE IMPORT PARAMETERS

Share

In this below post, I had published information about the parameters related to “Payables Open Interface Import” Program. Info from R12.1.1 instance.

Mandatory Parameters:

1.       Source: It can be any of the below list

Ø      Invoice Gateway - Invoice records you entered in the Invoice Gateway window

Ø      E-Commerce Gateway- Supplier invoices transferred through EDI

Ø      Credit Card- Procurement cards transactions from the credit card issuer

Ø      Oracle Property Manager- Lease invoices from Oracle Property Manager.

Ø      Oracle Assets- Lease payments from Oracle Assets.

Ø      User-defined- For invoice loaded from legacy Systems

2.       Invoice Batch Name

Ø      Use Batch Control Payables Profile option need to be enabled to use this parameter

Ø      Payables groups the invoices created from the invoice records you import and creates an invoice batch with the batch name you enter

Ø      While Re-importing the error invoices make sure the batch name remains the same

Optional Parameters:

3.       Operating Unit: Filtering predicate when null import program process invoice from all operating units.

4.       Hold Name: When this parameter is provided with the hold name, then the import program creates the invoices and blocks them with mentioned hold.

5.       Hold Reason: Corresponds to Hold Name

6.       GL Date: This value is used as a GL date for the invoices whose GL_DATE columns in interface tables are not populated.

7.       Purge. Enter Yes if you want Payables to delete from the Payables Open Interface tables all successfully imported invoice records that match the Source and Group ID of this import

8.       Summarize Report: Provides a Summarize report when ‘YES’