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,
       ra_customer_trx_lines_all rcta,
       ra_cust_trx_line_gl_dist_all rgl,
       xx_ra_customers rc,
       gl_code_combinations gcc,
       fnd_user f,
       hz_cust_site_uses_all rsu,
       xx_ra_addresses_all raa,
       hz_geographies ge,
       mtl_system_items msi,
       ar_payment_schedules_all apsa ,     ar_payment_schedules_all apsa_og
      , ra_customer_trx_lines_all rcta_og , ra_customer_trx_all rct_og
 WHERE rct.customer_trx_id = rcta.customer_trx_id
--   AND rct.customer_trx_id = rctpv.customer_trx_id
   AND rct.customer_trx_id = apsa.customer_trx_id
   AND rct_og.customer_trx_id = apsa_og.customer_trx_id
   AND rcta.customer_trx_line_id = rgl.customer_trx_line_id
   AND rct.bill_to_customer_id = rc.customer_id
   AND rgl.code_combination_id = gcc.code_combination_id
   AND f.user_id = rct.created_by
   AND rct.bill_to_site_use_id = rsu.site_use_id
   AND ge.geography_code = raa.country
   AND rsu.cust_acct_site_id = raa.address_id
   AND msi.inventory_item_id(+) = rcta.inventory_item_id
   AND rcta_og.customer_trx_line_id = rcta.previous_customer_trx_line_id
and    rct_og.customer_trx_id =   rcta_og.customer_trx_id
   AND rct.trx_number = '31721529'                   --21713974  cm - 31721529 
   AND rcta.line_type = 'LINE'
--   AND rcta.line_type NOT IN 'TAX'
--   AND ctt_type_name = 'Invoice-Mfg'
--   AND ctt_class = 'INV'
--AND RCT.TRX_NUMBER BETWEEN :P_TRX_FROM AND :P_TRX_TO

Comments

Post a Comment

Popular posts from this blog

Oracle Shipping Network SQL Query

-
Useful Script for Oracle Shipping Network , I wrote custom function for GL Code , you may find below SELECT a.from_organization_code, a.from_organization_name,        a.to_organization_code, a.to_organization_name,  DECODE (a.intransit_type,                  2, 'Intransit',                  1, 'Direct'                 ) "Transfer Type",        (SELECT meaning           FROM apps.mfg_lookups          WHERE lookup_type = 'MTL_FOB_POINT'            AND lookup_code = a.fob_point) fob_point,        a.elemental_visibility_enabled, DECODE (a.routing_header_id,                  1, 'Standard',                  2, 'Insp...
Click for more

Oracle Ar Invoice Numbering Sequence alter to avoid missing in between by cache

-
1) Query up your invoice source , which user have reported Jump in number. Navigate>Setup>Invoice>Source 2) Execute the following script using your source as in step 1.   SELECT batch_source_id, org_id, name FROM ra_batch_sources_all WHERE name LIKE '<source>' ;   or use this query if you have access from database.   SELECT 'alter sequence AR.' ||seq.sequence_name|| ' nocache;' FROM ( SELECT name bsname, org_id org, 'RA_TRX_NUMBER_' ||BATCH_SOURCE_ID|| '_' ||org_id|| '_S' seqname FROM RA_batch_sources_all ) src, dba_sequences seq WHERE src.seqname=seq.sequence_name AND seq.cache_size; ORDER BY org /     3) Using the batch_source_id from the above query , execute the followingscript:   SELECT sequence_owner, sequence_name, cache_size FROM all_sequences WHERE sequence_name LIKE 'RA_TRX_NUMBER%1146%' ;     4) Execute the following:   ALTER sequence SEQ...
Click for more

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...
Click for more