Query TO Find Chart Of Account Structure (COA)

select sob.name Ledger_Name
, sob.ledger_id Ledger_Id
, sob.chart_of_accounts_id coa_id
, fifst.id_flex_structure_name struct_name
, ifs.segment_name
, ifs.application_column_name column_name
, sav1.attribute_value BALANCING
, sav2.attribute_value COST_CENTER
, sav3.attribute_value NATURAL_ACCOUNT
, sav4.attribute_value INTERCOMPANY
, sav5.attribute_value SECONDARY_TRACKING
, sav6.attribute_value GLOBAL
, ffvs.flex_value_set_name
, ffvs.flex_value_set_id
from fnd_id_flex_structures fifs
, fnd_id_flex_structures_tl fifst
, fnd_segment_attribute_values sav1
, fnd_segment_attribute_values sav2
, fnd_segment_attribute_values sav3
, fnd_segment_attribute_values sav4
, fnd_segment_attribute_values sav5
, fnd_segment_attribute_values sav6
, fnd_id_flex_segments ifs
, fnd_flex_value_sets ffvs
, gl_ledgers sob
where 1=1
and fifs.id_flex_code = 'GL#'
and fifs.application_id = fifst.application_id
and fifs.id_flex_code = fifst.id_flex_code
and fifs.id_flex_num = fifst.id_flex_num
and fifs.application_id = ifs.application_id
and fifs.id_flex_code = ifs.id_flex_code
and fifs.id_flex_num = ifs.id_flex_num
and sav1.application_id = ifs.application_id
and sav1.id_flex_code = ifs.id_flex_code
and sav1.id_flex_num = ifs.id_flex_num
and sav1.application_column_name = ifs.application_column_name
and sav2.application_id = ifs.application_id
and sav2.id_flex_code = ifs.id_flex_code
and sav2.id_flex_num = ifs.id_flex_num
and sav2.application_column_name = ifs.application_column_name
and sav3.application_id = ifs.application_id
and sav3.id_flex_code = ifs.id_flex_code
and sav3.id_flex_num = ifs.id_flex_num
and sav3.application_column_name = ifs.application_column_name
and sav4.application_id = ifs.application_id
and sav4.id_flex_code = ifs.id_flex_code
and sav4.id_flex_num = ifs.id_flex_num
and sav4.application_column_name = ifs.application_column_name
and sav5.application_id = ifs.application_id
and sav5.id_flex_code = ifs.id_flex_code
and sav5.id_flex_num = ifs.id_flex_num
and sav5.application_column_name = ifs.application_column_name
and sav6.application_id = ifs.application_id
and sav6.id_flex_code = ifs.id_flex_code
and sav6.id_flex_num = ifs.id_flex_num
and sav6.application_column_name = ifs.application_column_name
and sav1.segment_attribute_type = 'GL_BALANCING'
and sav2.segment_attribute_type = 'FA_COST_CTR'
and sav3.segment_attribute_type = 'GL_ACCOUNT'
and sav4.segment_attribute_type = 'GL_INTERCOMPANY'
and sav5.segment_attribute_type = 'GL_SECONDARY_TRACKING'
and sav6.segment_attribute_type = 'GL_GLOBAL'
and ifs.id_flex_num = sob.chart_of_accounts_id
and ifs.flex_value_set_id = ffvs.flex_value_set_id
and sob.ledger_id =
nvl(fnd_profile.value('GL_SET_OF_BKS_ID'),sob.ledger_id)
order by sob.name, sob.chart_of_accounts_id, ifs.application_column_name;

Query TO Find Chart Of Account Structure (COA)

select sob.name Ledger_Name
, sob.ledger_id Ledger_Id
, sob.chart_of_accounts_id coa_id
, fifst.id_flex_structure_name struct_name
, ifs.segment_name
, ifs.application_column_name column_name
, sav1.attribute_value BALANCING
, sav2.attribute_value COST_CENTER
, sav3.attribute_value NATURAL_ACCOUNT
, sav4.attribute_value INTERCOMPANY
, sav5.attribute_value SECONDARY_TRACKING
, sav6.attribute_value GLOBAL
, ffvs.flex_value_set_name
, ffvs.flex_value_set_id
from fnd_id_flex_structures fifs
, fnd_id_flex_structures_tl fifst
, fnd_segment_attribute_values sav1
, fnd_segment_attribute_values sav2
, fnd_segment_attribute_values sav3
, fnd_segment_attribute_values sav4
, fnd_segment_attribute_values sav5
, fnd_segment_attribute_values sav6
, fnd_id_flex_segments ifs
, fnd_flex_value_sets ffvs
, gl_ledgers sob
where 1=1
and fifs.id_flex_code = 'GL#'
and fifs.application_id = fifst.application_id
and fifs.id_flex_code = fifst.id_flex_code
and fifs.id_flex_num = fifst.id_flex_num
and fifs.application_id = ifs.application_id
and fifs.id_flex_code = ifs.id_flex_code
and fifs.id_flex_num = ifs.id_flex_num
and sav1.application_id = ifs.application_id
and sav1.id_flex_code = ifs.id_flex_code
and sav1.id_flex_num = ifs.id_flex_num
and sav1.application_column_name = ifs.application_column_name
and sav2.application_id = ifs.application_id
and sav2.id_flex_code = ifs.id_flex_code
and sav2.id_flex_num = ifs.id_flex_num
and sav2.application_column_name = ifs.application_column_name
and sav3.application_id = ifs.application_id
and sav3.id_flex_code = ifs.id_flex_code
and sav3.id_flex_num = ifs.id_flex_num
and sav3.application_column_name = ifs.application_column_name
and sav4.application_id = ifs.application_id
and sav4.id_flex_code = ifs.id_flex_code
and sav4.id_flex_num = ifs.id_flex_num
and sav4.application_column_name = ifs.application_column_name
and sav5.application_id = ifs.application_id
and sav5.id_flex_code = ifs.id_flex_code
and sav5.id_flex_num = ifs.id_flex_num
and sav5.application_column_name = ifs.application_column_name
and sav6.application_id = ifs.application_id
and sav6.id_flex_code = ifs.id_flex_code
and sav6.id_flex_num = ifs.id_flex_num
and sav6.application_column_name = ifs.application_column_name
and sav1.segment_attribute_type = 'GL_BALANCING'
and sav2.segment_attribute_type = 'FA_COST_CTR'
and sav3.segment_attribute_type = 'GL_ACCOUNT'
and sav4.segment_attribute_type = 'GL_INTERCOMPANY'
and sav5.segment_attribute_type = 'GL_SECONDARY_TRACKING'
and sav6.segment_attribute_type = 'GL_GLOBAL'
and ifs.id_flex_num = sob.chart_of_accounts_id
and ifs.flex_value_set_id = ffvs.flex_value_set_id
and sob.ledger_id =
nvl(fnd_profile.value('GL_SET_OF_BKS_ID'),sob.ledger_id)
order by sob.name, sob.chart_of_accounts_id, ifs.application_column_name;

Query To Get Customer Details

SELECT hp.party_id
      ,hp.party_number
      ,hp.party_name
      ,hp.party_type
      ,hp.orig_system_reference
      ,hp.tax_reference
      ,hpua.party_usg_assignment_id
      ,hpua.party_usage_code
      ,hpua.effective_start_date
      ,hpua.effective_end_date
      ,hop.organization_profile_id
      ,hop.organization_name_phonetic
      ,hop.organization_type
      ,hps.party_site_id
      ,hps.party_site_number
      ,hps.orig_system_reference party_site_reference
      ,hps.party_site_name
      ,aps.vendor_id
      ,aps.vendor_name
      ,aps.vendor_name_alt
      ,aps.segment1
      ,aps.vendor_type_lookup_code
      ,aps.payment_currency_code
      ,aps.invoice_currency_code
      ,aps.hold_all_payments_flag
      ,aps.vat_registration_num
      ,aps.match_option
      ,apss.vendor_site_id
      ,apss.vendor_site_code
      ,apss.purchasing_site_flag
      ,apss.rfq_only_site_flag
      ,apss.pay_site_flag
      ,apss.address_line1
      ,apss.address_line2
      ,apss.zip
      ,apss.country
      ,apss.bill_to_location_id
      ,apss.accts_pay_code_combination_id
      ,iep.ext_payee_id
      ,iep.payment_function
      ,iep.org_type
      ,iep.default_payment_method_code
      ,iep.remit_advice_delivery_method
      ,iep.remit_advice_fax
      ,zxtp.party_type_code
      ,zxtp.rep_registration_number
FROM   hz_parties hp
      ,hz_party_usg_assignments hpua
      ,hz_organization_profiles hop
      ,hz_party_sites hps
      ,ap_suppliers aps
      ,ap_supplier_sites_all apss
      ,iby_external_payees_all iep
      ,zx_party_tax_profile zxtp
WHERE  1 = 1
AND    hp.party_id        = hpua.party_id
AND    hp.party_id        = hop.party_id
AND    hp.party_id        = hps.party_id
AND    hp.party_id        = aps.party_id
AND    aps.vendor_id      = apss.vendor_id
AND    hps.party_site_id  = apss.party_site_id
AND    hp.party_id        = iep.payee_party_id
AND    apss.vendor_site_id= iep.supplier_site_id
AND    hps.party_site_id  = iep.party_site_id
AND    hp.party_id        = zxtp.party_id

Query To Find Application URL

Select PROFILE_OPTION_VALUE
From   FND_PROFILE_OPTION_VALUES
WHERE  PROFILE_OPTION_ID =
       (SELECT PROFILE_OPTION_ID
        FROM FND_PROFILE_OPTIONS
        WHERE PROFILE_OPTION_NAME ='APPS_FRAMEWORK_AGENT')
        AND LEVEL_VALUE=0;

All Concurrent Program Run By User

SELECT
    user_concurrent_program_name,
    responsibility_name,
    request_date,
    argument_text,
    request_id,
    phase_code,
    status_code,
    logfile_name,
    outfile_name,
    output_file_type
FROM
    fnd_concurrent_requests fcr,
    fnd_concurrent_programs_tl fcp,
    fnd_responsibility_tl fr,
    fnd_user fu
WHERE
    fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
    and fcr.responsibility_id = fr.responsibility_id
    and fcr.requested_by = fu.user_id
    and user_name = upper(:user_name)
ORDER BY REQUEST_DATE DESC;

Request Details

SELECT
    user_concurrent_program_name,
    responsibility_name,
    request_date,
    argument_text,
    request_id,
    phase_code,
    status_code,
    logfile_name,
    outfile_name,
    output_file_type
FROM
    fnd_concurrent_requests fcr,
    fnd_concurrent_programs_tl fcp,
    fnd_responsibility_tl fr,
    fnd_user fu
WHERE
    fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
    and fcr.responsibility_id = fr.responsibility_id
    and fcr.requested_by = fu.user_id
    and user_name = upper(:user_name)
ORDER BY REQUEST_DATE DESC;

AR INTERFACE

DECLARE

    CURSOR C1 IS
    SELECT *
    FROM XX_UPLOAD_AR_INVOICES ar
    WHERE ar.PROCESS_FLAG IS NULL
    AND ar.SR_NO IN (3,4) ;
 
    v_trx_type      NUMBER;
    v_trx_name      VARCHAR2(60);
    v_batch_id      NUMBER;
    v_batch_name    VARCHAR2(60);
    v_gl_id         NUMBER;
    v_customer_id           NUMBER;
    v_bill_to_address_id    NUMBER;
    v_ship_to_address_id    NUMBER;
    V_TERM_ID       NUMBER;
    V_TERM          VARCHAR2(50);
    V_CC            NUMBER;
    V_ERR           VARCHAR2(4000);
    V_SITE          VARCHAr2(150);
    V_INTERFACE_LINE_ID     NUMBER;
 
 
BEGIN
 
    FOR i IN C1
    LOOP          
     
        V_ERR := Null;
     
        BEGIN
            /* ---- Check Batch Source Name ------ */
            SELECT rb.BATCH_SOURCE_ID, rb.NAME
            INTO v_batch_id, v_batch_name
            FROM RA_BATCH_SOURCES_ALL rb
            WHERE rb.NAME = 'Open Data Import Source'
            AND rb.ORG_ID = 82 ;
         
         
            /* -----  Check Transaction Type  -----*/
            SELECT rct.CUST_TRX_TYPE_ID, rct.NAME, rct.SET_OF_BOOKS_ID
            INTO v_trx_type, v_trx_name, v_gl_id
            FROM RA_CUST_TRX_TYPES_ALL rct
            WHERE rct.NAME = 'NEEPL_MCS_DOM_INV'  --- ar.CLASS                    
            AND rct.ORG_ID = 82 ;
         
            /* ----- Check Term Information ------ */
            BEGIN
         
             SELECT rt.TERM_ID, rt.NAME INTO V_TERM_ID, V_TERM
                FROM RA_TERMS rt
                WHERE UPPER(rt.NAME) = UPPER( i.PAYMENT_TERM );

            EXCEPTION
                WHEN OTHERS THEN
                     V_TERM_ID := 5;
            END;
         
            BEGIN
         
            SELECT hca.CUST_ACCOUNT_ID INTO v_customer_id
            FROM HZ_PARTIES hp,
            HZ_CUST_ACCOUNTS hca
            WHERE hp.PARTY_ID = hca.PARTY_ID
            AND UPPER(RTRIM(LTRIM(hp.PARTY_NAME))) = UPPER(RTRIM(LTRIM(i.BILL_TO_NAME)));
         
            EXCEPTION
                WHEN OTHERS THEN
                     v_customer_id := NULL;
                     V_ERR := V_ERR || ' Supplier Not Exists.';
         
            END;
            /* ----- Bill to address id ------ */
            BEGIN
                SELECT hcas.CUST_ACCT_SITE_ID INTO v_bill_to_address_id
                FROM hz_cust_accounts_all hca
                ,hz_cust_acct_sites_all hcas
                ,hz_cust_site_uses_all hcsu
                WHERE 1 = 1
                AND hca.cust_account_id = hcas.cust_account_id
                AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
                AND hcsu.status = 'A'
                AND hcsu.site_use_code = 'BILL_TO'
                AND hca.cust_account_id = v_customer_id
                AND hcsu.org_id = 82;
             
            EXCEPTION
                WHEN OTHERS THEN
                    v_bill_to_address_id := NULL;
                    V_ERR := V_ERR || ' Bill to Addrees not exist';
            END;
         
            BEGIN

                /* ----- Ship to Address ID ------ */
                SELECT hcas.CUST_ACCT_SITE_ID INTO v_ship_to_address_id
                FROM hz_cust_accounts_all hca
                ,hz_cust_acct_sites_all hcas
                ,hz_cust_site_uses_all hcsu
                WHERE 1 = 1
                AND hca.cust_account_id = hcas.cust_account_id
                AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
                AND hcsu.status = 'A'
                AND hcsu.site_use_code = 'SHIP_TO'
                AND hca.cust_account_id = v_customer_id
                AND hcsu.org_id = 82;

            EXCEPTION
                WHEN OTHERS THEN
                    v_ship_to_address_id := NULL;

            END;
       
        IF v_customer_id IS NOT NULL AND    v_bill_to_address_id IS NOT NULL THEN
     
            SELECT RA_CUSTOMER_TRX_LINES_S.NEXTVAL INTO V_INTERFACE_LINE_ID
            FROM DUAL;
         
            BEGIN
         
                SELECT ffv.FLEX_VALUE INTO V_SITE
                FROM FND_FLEX_VALUES_VL ffv
                WHERE ffv.FLEX_VALUE_SET_ID = 1014868
                AND UPPER(ffv.DESCRIPTION) = UPPER(i.SITE) ;

            EXCEPTION
                WHEN OTHERS THEN
                    V_SITE := NULL;
            END;
         
            INSERT INTO ra_interface_lines_all ria
            ( INTERFACE_LINE_ID,BATCH_SOURCE_NAME,LINE_TYPE,
                ria.TRX_NUMBER,
                CUST_TRX_TYPE_ID,
                cust_trx_type_name,
                SET_OF_BOOKS_ID,
                TRX_DATE,
                GL_DATE,
                CURRENCY_CODE,
                term_id,
                term_name,
                orig_system_bill_customer_id,
                ORIG_SYSTEM_BILL_CUSTOMER_REF,
                orig_system_bill_address_id,
                ORIG_SYSTEM_BILL_ADDRESS_REF,
                ria.ORIG_SYSTEM_SHIP_ADDRESS_ID,
                ria.ORIG_SYSTEM_SHIP_ADDRESS_REF,
                ria.INVENTORY_ITEM_ID,
                QUANTITY,
                ria.UNIT_SELLING_PRICE,
                AMOUNT,
                DESCRIPTION,
                org_id,
                CONVERSION_TYPE,
                ria.CONVERSION_RATE,
                ria.INTERFACE_LINE_CONTEXT,
                ria.INTERFACE_LINE_ATTRIBUTE1,
                ria.INTERFACE_LINE_ATTRIBUTE2,
                ria.INTERFACE_LINE_ATTRIBUTE3,
                ria.INTERFACE_LINE_ATTRIBUTE4,
                ria.INTERFACE_LINE_ATTRIBUTE5,
                ria.INTERFACE_LINE_ATTRIBUTE6,
                ria.INTERFACE_LINE_ATTRIBUTE7,
                ria.INTERFACE_LINE_ATTRIBUTE8,
                ria.INTERFACE_LINE_ATTRIBUTE9,
                ria.INTERFACE_LINE_ATTRIBUTE10,
                ria.INTERFACE_LINE_ATTRIBUTE11,
                ria.INTERFACE_LINE_ATTRIBUTE12,
                ria.INTERFACE_LINE_ATTRIBUTE13,
                ria.INTERFACE_LINE_ATTRIBUTE14,
                ria.INTERFACE_LINE_ATTRIBUTE15,
                ria.PRIMARY_SALESREP_NUMBER,
                ria.HEADER_ATTRIBUTE1,
                ria.HEADER_ATTRIBUTE2,
                ria.HEADER_ATTRIBUTE3
            )Values
            (V_INTERFACE_LINE_ID,
                'Open Data Import Source',
                'LINE',
                NULL,
                v_trx_type,                 -- cust_trx_type_id,
                'NEEPL_MCS_DOM_INV',        -- cust_trx_type_name,
                v_gl_id,
                i.INV_DATE ,
                '28-FEB-2013',
                'INR',                      -- currency_code,
                V_TERM_ID,                  -- term_id,
                V_TERM,                    -- term_name,
                v_customer_id,
                v_customer_id,
                v_bill_to_address_id,
                v_bill_to_address_id,
                v_ship_to_address_id,
                v_ship_to_address_id,
                NULL,                       --- Inventory item id
                1,                          --- Qty
                i.AMOUNT,                      --- Unit Selling Price
                i.AMOUNT,                      --- Invoice Amount
                'Test Invoice',             --- Description
                82,                         --- Orgid
                'User',                     --- Conversion Type
                1,                          --- Convesrion rate
                'ORDER ENTRY',
                i.BILL_TO_NUMBER ,
                '0',
                '0',
                '0',
                '0',
                '0',
                '0',
                '0',
                '0',
                '0',
                '0',
                '0',
                '0',
                '0',
                '0',
                -3,      ---primary saleperson -- No Sales Credits
                i.DIVISION,
                V_SITE,
                i.BILL_TO_NUMBER
            );

            COMMIT;
         

            BEGIN
             
                         
                SELECT gcc.CODE_COMBINATION_ID INTO V_CC
                FROM GL_CODE_COMBINATIONS gcc
                WHERE gcc.SEGMENT1||'.'||gcc.SEGMENT2||'.'||
                gcc.SEGMENT3||'.'||gcc.SEGMENT4||'.'||gcc.SEGMENT5||'.'||
                gcc.SEGMENT6||'.'||gcc.SEGMENT7||'.'||gcc.SEGMENT8 = TRIM(i.ACCOUNT_CODE);

            EXCEPTION
                WHEN OTHERS THEN
                         V_CC := NULL;
            END;
             
            INSERT INTO ra_interface_distributions_all rid
            (
            INTERFACE_LINE_ID
            ,account_class
            ,amount
            ,code_combination_id
            ,percent
            ,org_id
            )
            VALUES
            (
            V_INTERFACE_LINE_ID,
            'REV'
            ,i.AMOUNT                              --- Invoice Amt
            ,V_CC                               --- Code Combination ID
            ,100                              
            ,82
            );

            COMMIT;
         
            INSERT INTO RA_INTERFACE_SALESCREDITS_ALL rs
            (rs.INTERFACE_LINE_ID,
            rs.INTERFACE_LINE_CONTEXT,
            rs.INTERFACE_LINE_ATTRIBUTE1,
            rs.INTERFACE_LINE_ATTRIBUTE2,
            rs.INTERFACE_LINE_ATTRIBUTE3,
            rs.INTERFACE_LINE_ATTRIBUTE4,
            rs.INTERFACE_LINE_ATTRIBUTE5,
            rs.INTERFACE_LINE_ATTRIBUTE6,
            rs.INTERFACE_LINE_ATTRIBUTE7,
            rs.INTERFACE_LINE_ATTRIBUTE8,
            rs.INTERFACE_LINE_ATTRIBUTE9,
            rs.INTERFACE_LINE_ATTRIBUTE10,
            rs.INTERFACE_LINE_ATTRIBUTE11,
            rs.INTERFACE_LINE_ATTRIBUTE12,
            rs.INTERFACE_LINE_ATTRIBUTE13,
            rs.INTERFACE_LINE_ATTRIBUTE14,
            rs.INTERFACE_LINE_ATTRIBUTE15,
            rs.CREATION_DATE,
            rs.CREATED_BY,
            rs.SALES_CREDIT_AMOUNT_SPLIT,
            rs.SALES_CREDIT_PERCENT_SPLIT,
            rs.SALESREP_NUMBER,
            rs.SALESREP_ID,
            rs.SALES_CREDIT_TYPE_ID,
            rs.SALES_CREDIT_TYPE_NAME,
            rs.ORG_ID )
            VALUES
            (V_INTERFACE_LINE_ID,
            'ORDER ENTRY',
            i.BILL_TO_NUMBER,
            '0',
            '0',
            '0',
            '0',
            '0',
            '0',
            '0',
            '0',
            '0',
            '0',
            '0',
            '0',
            '0',  
            '0',
            SYSDATE,
            1110,
            i.AMOUNT,                                  -- Amount
            100,
            -3,
            NULL,
            1,
            'Quota Sales Credit',
            82
            );
         
            COMMIT;
         
            UPDATE XX_UPLOAD_AR_INVOICES ar
                SET ar.PROCESS_FLAG = 'Y'
                , ar.ERROR_MESSAGE = NULL
                WHERE ar.SR_NO = i.SR_NO;
                COMMIT;
     
        ELSE
         
            UPDATE XX_UPLOAD_AR_INVOICES ar
                SET ar.PROCESS_FLAG = 'E'
                , ar.ERROR_MESSAGE = V_ERR
                WHERE ar.SR_NO = i.SR_NO;
                COMMIT;
         
        END IF;
     
        EXCEPTION
            WHEN OTHERS THEN
                V_ERR := SQLCODE || '-'|| SQLERRM;
               
                UPDATE XX_UPLOAD_AR_INVOICES ar
                SET ar.PROCESS_FLAG = 'EE'
                , ar.ERROR_MESSAGE = V_ERR
                WHERE ar.SR_NO = i.SR_NO;
                COMMIT;
        END;
    END LOOP;
 
END;