Posts

Query to find DFF

SELECT b.application_table_name , b.descriptive_flexfield_name , a.application_column_name , a.end_user_column_name , a.descriptive_flex_context_code FROM fnd_descr_flex_column_usages a , fnd_descriptive_flexs_vl b WHERE 1 = 1 AND b.descriptive_flexfield_name = a.descriptive_flexfield_name AND b.application_id = a.application_id AND A.CREATED_BY not in (0, 1,2, 5, 120, 121) and b.application_table_name like '%PO%VENDO%' AND b.application_table_name != 'FND_SRS_MASTER' ORDER BY 1,4

How to call Auto invoice Import Program from Back end

Autoinvoice Import Program takes following parameters : argv[0]  program name argv[1]  parallel module name  [MAIN|PU?] argv[2]  running mode  [V|T|P] argv[3]  batch source id argv[4]  batch source name argv[5]  default date argv[6]  concatenated transaction flexfield attribute value argv[7]  transaction type id argv[8]  low bill to customer number argv[9]  high bill to customer number argv[10]  low bill to customer name argv[11] high bill to customer name argv[12] low gl date argv[13] high gl date argv[14] low ship date argv[15] high ship date argv[16] low transaction number argv[17] high transaction number argv[18] low sales order argv[19] high sales order argv[20] low invoice date argv[21] high invoice date argv[22] low ship to customer number argv[23] high ship to customer number argv[24] low ship to customer name argv[25] high ship to customer name argv[26] Call RAXTRX from RAXMTR flag ar...

Oracle Property table list in oracle apps

Property: PN_PROPERTIES_ALL PN_LOCATIONS_ALL Leases : PN_LEASES_ALL PN_LEASE_DETAILS_ALL PN_OPTIONS_ALL PN_PAYMENT_TERMS_ALL PN_DISTRIBUTIONS_ALL Variable Rent Agreement: PN_VAR_LINES_ALL PN_VAR_PERIODS_ALL PN_VAR_RENTS_ALL PN_VAR_RENT_DATES_ALL PN_VAR_RENT_INV_ALL PN_VAR_RENT_SUMM_ALL Full list is here: PN_ACCOUNTING_EVENTS_ALL PN_ADDRESSES_ALL PN_AE_HEADERS_ALL PN_AE_LINES_ALL PN_COMPANIES_ALL PN_COMPANY_SITES_ALL PN_CONTACTS_ALL PN_CONTACT_ASSIGNMENTS_ALL PN_CONTACT_ASSIGN_HISTORY PN_CURRENCIES PN_DISTRIBUTIONS_ALL PN_EMP_SPACE_ASSIGN_ITF PN_INDEX_EXCLUDE_TERM_ALL PN_INDEX_HISTORY_HEADERS PN_INDEX_HISTORY_LINES PN_INDEX_LEASES_ALL PN_INDEX_LEASE_CONSTRAINTS_ALL PN_INDEX_LEASE_PERIODS_ALL PN_INDEX_LEASE_TERMS_ALL PN_INSURANCE_REQUIREMENTS_ALL PN_INSUR_REQUIRE_HISTORY PN_LANDLORD_SERVICES_ALL PN_LANDLORD_SERVICE_HISTORY PN_LEASES_ALL PN_LEASE_CHANGES_ALL PN_LEASE_DETAILS_ALL PN_LEASE_DETAILS_HISTORY PN_LEASE_MILESTONES_ALL PN_LEASE_OPTIONS_ITF PN_LEASE_TRANSACTIONS_ALL PN_LOCATIO...

Oracle AR Credit Note Script

SELECT ROWNUM, rc.customer_name customer_name, rct_og.trx_number trx_number,rct.trx_number credit_note_num ,       rct.trx_date trx_date, msi.segment1 item_code,        rcta.quantity_invoiced quantity, rcta.description description,        TRUNC (rct_og.creation_date) "CREATION_DATE", f.user_name user_name,        xx_tax_utl_pub.get_tax_reg_num (rc.customer_id,                                        rct.org_id) tax_reg_num,        raa.address1 address1, raa.address2 address2,        apsa_og.amount_line_items_original original_amount,        apsa_og.amount_line_items_remaining credit_amount, rcta.line_type    FROM ra_customer_trx_all rct, --       ra_customer_trx_partial_v rctpv,      ...

Oracle Virtual Private Database

What is VPD VPD combines these two features, enabling you to enforce security policies to control access at the row level. This control is based on application or session attributes, which can be made available during execution. CREATE OR REPLACE FUNCTION hide_sal_comm (  v_schema IN VARCHAR2,   v_objname IN VARCHAR2) RETURN VARCHAR2 AS con VARCHAR2 (200); BEGIN -- con := 'LINE_TYPE_LOOKUP_CODE=''ITEM'''; con := 'INVOICE_ID=81552';  RETURN (con); END hide_sal_comm; BEGIN  DBMS_RLS.ADD_POLICY (   object_schema     => 'apps',    object_name       => 'AP_INVOICE_LINES_ALL',   policy_name       => 'hide_sal_policy',    policy_function   => 'hide_sal_comm',   sec_relevant_cols => null); END; BEGIN    -- to drop a existing policy    DBMS_RLS.drop_POLICY (object_schema  ...

how to extract Oracle Fixed Asset Projection report query

Oracle projection report concurrent request, it runs in two parts. The first part builds a table of projection info. The second part is a spawned job that prints the report.  Register a concurrent request that only runs the first part and use sql against the temporary table that is generated. -- This code assumes fadeprn_temp exists - this is a copy of any fa_proj_interim_x table -- P1 = Book -- P2 = Period DECLARE    req_id         NUMBER;     req_id1         NUMBER;    output_file    UTL_FILE.file_type;    errbuf         VARCHAR2 (240);    v_context      VARCHAR2 (100);    v_phase_code   VARCHAR2 (5);    v_stmt_str     VARCHAR2 (2000); -- Report generator    CURSOR c1    IS       SELECT      'Comp_'             ...

Oracle SQL Pivot Query Example

select * from ( SELECT DISTINCT  dhcc.segment2 business_unit, dhcc.segment3 ACCOUNT,                 dhcc.segment4 dept, dhcc.segment5, fc.concatenated_segments,fpip.DEPRECIATION,fdp.Period_name             FROM  fadeprn_temp fpip ,  fa_additions fa,                 fa_deprn_periods fdp,                 fa.fa_deprn_summary fds,                 fa_categories_b_kfv fc,                 fa_distribution_history dh,                 gl_code_combinations dhcc           WHERE fa.asset_id = fds.asset_id             AND fdp.period_counter = fds.period_counter             AND dhcc.code_combination_id(+) = dh.code_combinat...