Interface tables in Oracle Apps R12

ORACLE FINANCIALS – PAYABLES - INVOICES

AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE

ORACLE FINANCIALS – RECEIVABLES - CUSTOMERS

RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL
HZ_PARTY_INTERFACE

ORACLE FINANCIALS – RECEIVABLES - INVOICES

RA_INTERFACE_LINES_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL

ORACLE FINANCIALS – RECEIVABLES – LOCK BOX

AR_PAYMENTS_INTERFACE_ALL

ORACLE FINANCIALS – RECEIVABLES - TAX

AR_TAX_INTERFACE

ORACLE FINANCIALS - CASH MANAGEMENT – BANK STATEMENTS

CE_STATEMENT_HEADERS_INT_ALL
CE_STATEMENT_LINES_INTERFACE

ORACLE FINANCIALS - FIXED ASSETS

FA_INV_INTERFACE

ORACLE FINANCIALS - GENERAL LEDGER – JOURNAL ENTRY

GL_INTERFACE

ORACLE MANUFACTURING – COST MANAGEMENT

CST_PC_ITEM_COST_INTERFACE
CST_PC_COST_DET_INTERFACE

ORACLE MANUFACTURING - INVENTORY

MTL_REPLENISH_HEADERS_INT
MTL_REPLENISH_LINES_INT
MTL_SERIAL_NUMBERS_INTERFACE
MTL_SYSTEM_ITEMS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_TRANSACTIONS_INTERFACE

ORACLE MANUFACTURING – PURCHASING – PURCHASE ORDERS

PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_RESCHEDULE_INTERFACE

ORACLE MANUFACTURING – PURCHASING - RECEIVING

RCV_HEADERS_INTERFACE
RCV_TRANSACTIONS_INTERFACE
RCV_LOTS_INTERFACE
RCV_SERIALS_INTERFACE

ORACLE MANUFACTURING - ORDER ENTRY – SALES ORDERS

SO_HEADERS_INTERFACE_ALL
SO_HEADER_ATTRIBUTES_INTERFACE
SO_LINES_INTERFACE_ALL
SO_LINE_ATTRIBUTES_INTERFACE
SO_LINE_DETAILS_INTERFACE
SO_PRICE_ADJUSTMENTS_INTERFACE
SO_SALES_CREDITS_INTERFACE

ORACLE MANUFACTURING - MASTER SCHEDULING/MRP

MRP_FORECAST_INTERFACE
MRP_SCHEDULE_INTERFACE

ORACLE MANUFACTURING - SHOP FLOOR MANAGEMENT

WSM_LOT_JOB_INTERFACE
WSM_STARTING_LOTS_INTERFACE
WSM_LOT_MOVE_TXN_INTERFACE

ORACLE MANUFACTURING - QUALITY

QA_RESULTS_INTERFACE

ORACLE MANUFACTURING - WORK IN PROCESS

WIP_MOVE_TXN_INTERFACE
CST_COMP_SNAP_INTERFACE
WIP_COST_TXN_INTERFACE
WIP_JOB_SCHEDULE_INTERFACE
WIP_JOB_DTLS_INTERFACE

--****************************************************

Interface Tables of GL, AP, AR,FA,INV,PO,BOMS,WIP,OM
GL INTERFACE TABLES

GL_BUDGET_INTERFACE
GL_DAILY_RATES_INTERFACE
GL_IEA_INTERFACE
GL_INTERFACE
GL_INTERFACE_CONTROL
GL_INTERFACE_HISTORY

AP INTERFACE TABLES
AP_INTERFACE_CONTROLS
AP_INTERFACE_REJECTIONS
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE

AR INTERFACE TABLES
AR_PAYMENTS_INTERFACE_ALL
AR_TAX_INTERFACE
HZ_DQM_SYNC_INTERFACE
HZ_PARTY_INTERFACE
HZ_PARTY_INTERFACE_ERRORS
RA_CUSTOMERS_INTERFACE_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_ERRORS_ALL
RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS_ALL

FA INTERFACE TABLES
FA_BUDGET_INTERFACE
FA_INV_INTERFACE
FA_PRODUCTION_INTERFACE
FA_TAX_INTERFACE

INVENTORY INTERFACE TABLES
MTL_CC_ENTRIES_INTERFACE
MTL_CC_INTERFACE_ERRORS
MTL_CI_INTERFACE
MTL_CI_XREFS_INTERFACE
MTL_COPY_ORG_INTERFACE
MTL_CROSS_REFERENCES_INTERFACE
MTL_DEMAND_INTERFACE
MTL_DESC_ELEM_VAL_INTERFACE
MTL_EAM_ASSET_NUM_INTERFACE
MTL_EAM_ATTR_VAL_INTERFACE
MTL_INTERFACE_ERRORS
MTL_INTERFACE_PROC_CONTROLS
MTL_ITEM_CATEGORIES_INTERFACE
MTL_ITEM_CHILD_INFO_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE
MTL_ITEM_SUB_INVS_INTERFACE
MTL_OBJECT_GENEALOGY_INTERFACE
MTL_RELATED_ITEMS_INTERFACE
MTL_RESERVATIONS_INTERFACE
MTL_RTG_ITEM_REVS_INTERFACE
MTL_SECONDARY_LOCS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE
MTL_SO_RMA_INTERFACE
MTL_SYSTEM_ITEMS_INTERFACE
MTL_TRANSACTIONS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_TXN_COST_DET_INTERFACE

PO INTERFACE TABLES
PO_DISTRIBUTIONS_INTERFACE
PO_HEADERS_INTERFACE
PO_INTERFACE_ERRORS
PO_LINES_INTERFACE
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_RESCHEDULE_INTERFACE
RCV_HEADERS_INTERFACE
RCV_LOTS_INTERFACE
RCV_SERIALS_INTERFACE
RCV_TRANSACTIONS_INTERFACE


BOM INTERFACE TABLES
BOM_BILL_OF_MTLS_INTERFACE
BOM_INTERFACE_DELETE_GROUPS
BOM_INVENTORY_COMPS_INTERFACE
BOM_OP_RESOURCES_INTERFACE
BOM_OP_ROUTINGS_INTERFACE
BOM_OP_SEQUENCES_INTERFACE
BOM_REF_DESGS_INTERFACE
BOM_SUB_COMPS_INTERFACE
CST_COMP_SNAP_INTERFACE
CST_INTERFACE_ERRORS
CST_ITEM_COSTS_INTERFACE
CST_ITEM_CST_DTLS_INTERFACE
CST_PC_COST_DET_INTERFACE
CST_PC_ITEM_COST_INTERFACE

WIP INTERFACE TABLES
WIP_COST_TXN_INTERFACE
WIP_INTERFACE_ERRORS
WIP_JOB_DTLS_INTERFACE
WIP_JOB_SCHEDULE_INTERFACE
WIP_MOVE_TXN_INTERFACE
WIP_SCHEDULING_INTERFACE
WIP_TXN_INTERFACE_ERRORS TABLE

ORDER MANAGEMENT INTERFACE TABLES
SO_CONFIGURATIONS_INTERFACE
SO_HEADERS_INTERFACE_ALL
SO_HEADER_ATTRIBUTES_INTERFACE
SO_LINES_INTERFACE_ALL
SO_LINE_ATTRIBUTES_INTERFACE
SO_LINE_DETAILS_INTERFACE
SO_PRICE_ADJUSTMENTS_INTERFACE
SO_SALES_CREDITS_INTERFACE
SO_SERVICE_DETAILS_INTERFACE
WSH_DELIVERIES_INTERFACE
WSH_FREIGHT_CHARGES_INTERFACE

WSH_PACKED_CONTAINER_INTERFACE 

Load flex values for value set using API in oracle Apps R12

CREATE OR REPLACE PACKAGE BODY APPS.XXRAN_FLEX_VALUE_UPLOAD_PKG
AS
------------------------- MAIN Procedure --------------------------------           
PROCEDURE FLEX_field_value_load (p_errbuf         OUT VARCHAR2
                                ,p_retcode        OUT NUMBER )  IS
                CURSOR lcu_seg_values IS
                        SELECT TRIM(ls_value_set_name) value_set_name,
                               TRIM(ls_flex_value) flex_value,
                               TRIM(ls_enable_flag) enable_flag,
                               TRIM(ls_compiled_attr) compiled_attr,
                               TRIM(start_date_active) start_date_active,
                               TRIM(end_date_active) end_date_active,
                               TRIM(ls_meaning) meaning,
                               TRIM(ls_description) description,
                               TRIM(value_category) value_category                           
                          FROM XXRAN_FND_SEG_VALUE_STG
 -- STAGING TABLE HAVING RECORDS TO BE LOADED INTO FLEX VALUE
                         WHERE PROCESS_FLAG = 'P';
                    lc_error_flag           VARCHAR2(1);
                    lc_flex_value_set_name  VARCHAR2(100);
                    ln_flex_value_set_id    NUMBER;
                    lc_flex_value           VARCHAR2(100);
                    lc_flex_value_id        VARCHAR2(100);
                    lc_flex_value_meaning   varchar2(100);
                    lc_error_msg            varchar2(1000);
                    error                   VARCHAR2(400);
                    ln_length_flex_val      number;
                    ln_display_size         number;
                    ln_flex_value_count     number;
                    lc_error_loc             varchar2(1000);
                    ln_flex_length_validate  varchar2(20);
         BEGIN ---Validate Procedure
              MO_GLOBAL.set_policy_context('S',TO_NUMBER(FND_PROFILE.VALUE('ORG_ID')));
              fnd_file.put_line (fnd_file.LOG, 'Start of Log Messages: ');
              fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------------------------- '|| CHR (10));
                 p_errbuf := NULL;
                 p_retcode := 0;
                 lc_error_flag    :='N';
                 ln_length_flex_val :=0;
                 ln_display_size    := 0;
                 ln_flex_value_count :=0;
                 ln_flex_length_validate :=0;
         
            FOR lr_seg_values IN lcu_seg_values
            LOOP    
                 lc_error_flag    :='';
                 lc_flex_value_set_name :=NULL;
                 ln_flex_value_set_id :=NULL;
                 fnd_file.put_line(fnd_file.LOG,'Segment Value Upload is executing for Flex Value Set = '||
                                    lr_seg_values.value_set_name ||'Flex Value = '||lr_seg_values.flex_value);
           -- Validate Flex value set Name and get flex value set ID
           BEGIN
                SELECT flex_value_set_id
                  INTO ln_flex_value_set_id
                  FROM FND_FLEX_VALUE_SETS
                 WHERE UPPER(flex_value_set_name) = UPPER(lr_seg_values.value_set_name) ;
           EXCEPTION
                 WHEN NO_DATA_FOUND THEN
                 lc_error_msg := 'Error : value set not found : '||lr_seg_values.value_set_name;
                 ln_flex_value_set_id := NULL;
                 WHEN OTHERS THEN
                 ln_flex_value_set_id := NULL;
           END;
        
          IF ln_flex_value_set_id  is not null then
                 
                BEGIN  -- CHECK FOR FLEX VALUE EXISTANCE
                        SELECT flex_value_id
                        INTO ln_flex_value_id
                        FROM FND_FLEX_VALUES
                        WHERE upper(flex_value)= upper(lr_seg_values.flex_value)
                        AND flex_value_set_id = ln_flex_value_set_id;
               EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                     lc_flex_value_id := 'S';
                     when too_many_rows then
                     lc_error_msg := 'Error : Too many Flex values found : '||ln_flex_value_set_id;
                     FND_FILE.PUT_LINE(FND_FILE.LOG,lc_error_msg);
                     lc_flex_value_id :=  'E';             
                     WHEN OTHERS THEN             
                     lc_error_msg := 'Error : Invlaid Flex value : '||ln_flex_value_set_id;
                     FND_FILE.PUT_LINE(FND_FILE.LOG,lc_error_msg);
                     lc_flex_value_id := 'E';
               END;
                
            
               IF  lc_flex_value_id = 'S' then
                  -----------------------Validate length of  flex value ------------------------------------
                    BEGIN
                          SELECT length(p_flex_value)
                            INTO ln_length_flex_val
                            FROM DUAL ;
                    EXCEPTION
                         WHEN NO_DATA_FOUND THEN
                         lc_error_msg := 'Error : flex value length : '||p_flex_value;
                         FND_FILE.PUT_LINE(FND_FILE.LOG,lc_error_msg);
                         ln_flex_length_validate := 'E';
                    END;
                     IF ln_length_flex_val > ln_display_size /* TAKE DISPLAY SIZE FROM SEGMENT LIKE 50*/ THEN
                     ln_flex_length_validate :=  'E';
                     else
                     ln_flex_length_validate :=  'S';
                     END IF;
               END IF;
          
           end if;
          END LOOP;
       
     EXCEPTION
       WHEN OTHERS THEN
           FND_FILE.PUT_LINE(FND_FILE.LOG,'Error executing the program : '||SUBSTR(SQLERRM,1,255));
           p_errbuf := SUBSTR(SQLERRM,1,255);
           p_retcode := -1;
     END FLEX_field_value_load;
          PROCEDURE upload (p_VALUE_SET_NAME in varchar2,p_FLEX_VALUE varchar2,
                           p_MEANING in varchar2,p_DESCRIPTION in varchar2 )  IS
           BEGIN  
               fnd_flex_loader_apis.up_value_set_value(  p_upload_phase               =>'BEGIN',
                                               p_upload_mode                =>'',
                                               p_custom_mode                =>'',
                                               p_flex_value_set_name        => p_VALUE_SET_NAME,
                                               p_parent_flex_value_low      =>'',
                                               p_flex_value                 =>p_FLEX_VALUE,
                                               p_owner                      =>'',
                                               p_last_update_date           => to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),
                                               p_enabled_flag               =>'Y',
                                               p_summary_flag               =>'N',
                                               p_start_date_active          =>'',
                                               p_end_date_active            =>'',
                                               p_parent_flex_value_high     =>'',
                                               p_rollup_flex_value_set_name =>'',
                                               p_rollup_hierarchy_code      =>'',
                                               p_hierarchy_level            =>'',
                                               p_compiled_value_attributes  =>'',
                                               p_flex_value_meaning         =>p_MEANING,
                                               p_description                =>p_DESCRIPTION
                                               );                               
            END upload ;                
end XXRAN_FLEX_VALUE_UPLOAD_PKG;