General Queries

----Program, Resp, Application-----

SELECT DISTINCT

  FCPL.USER_CONCURRENT_PROGRAM_NAME

, FCP.CONCURRENT_PROGRAM_NAME

, FAPP.APPLICATION_NAME

, FRG.REQUEST_GROUP_NAME

, FNRTL.RESPONSIBILITY_NAME

,fcp.output_file_type

FROM

  FND_REQUEST_GROUPS FRG

,FND_APPLICATION_TL FAPP

, FND_REQUEST_GROUP_UNITS FRGU

, FND_CONCURRENT_PROGRAMS FCP

,FND_CONCURRENT_PROGRAMS_TL FCPL

, FND_RESPONSIBILITY FNR

, FND_RESPONSIBILITY_TL FNRTL

,fnd_executables_form_v FE

WHERE

    FRG.APPLICATION_ID=fapp.APPLICATION_ID

AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID

AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID

AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID

AND FRG.APPLICATION_ID = FNR.APPLICATION_ID

AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID

AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID

AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID

AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID

AND FE.application_id = FAPP.application_id

AND FE.executable_id = FCP.executable_id

and FAPP.APPLICATION_NAME like 'Modo%'

--AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE :conc_prg_name

AND FNRTL.LANGUAGE = 'US'

AND FAPP.LANGUAGE = 'US'; 

----Completed error-----------

SELECT a.request_id "Req Id"

,a.phase_code,a.status_code

, actual_start_date

, actual_completion_date

,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"

FROM fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b

,fnd_concurrent_queues q

,fnd_concurrent_programs c

,fnd_concurrent_programs_tl ctl

WHERE a.controlling_manager = b.concurrent_process_id

AND a.concurrent_program_id = c.concurrent_program_id

AND a.program_application_id = c.application_id

AND a.status_code = 'E'

AND a.phase_code = 'C'

AND actual_start_date > sysdate - 8

AND b.queue_application_id = q.application_id

AND b.concurrent_queue_id = q.concurrent_queue_id

AND ctl.concurrent_program_id = c.concurrent_program_id

AND ctl.LANGUAGE = 'US'

ORDER BY 5 DESC;

---- requestid, program name------------

SELECT a.request_id "Req Id"

,a.phase_code,a.status_code

, actual_start_date

, actual_completion_date

,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"

,a.number_of_copies

,a.printer

,a.ARGUMENT1

,a.ARGUMENT2

,a.ARGUMENT3

FROM fnd_Concurrent_requests a,fnd_concurrent_processes b

,fnd_concurrent_queues q

,fnd_concurrent_programs c

,fnd_concurrent_programs_tl ctl

WHERE a.controlling_manager = b.concurrent_process_id

AND a.concurrent_program_id = c.concurrent_program_id

AND a.program_application_id = c.application_id

AND b.queue_application_id = q.application_id

AND b.concurrent_queue_id = q.concurrent_queue_id

AND ctl.concurrent_program_id = c.concurrent_program_id

and a.request_id =5012381

AND ctl.LANGUAGE = 'US'

ORDER BY 5 DESC;

SELECT request_id, user_concurrent_program_name,

DECODE(phase_code,'C','Completed',phase_code) phase_code,

DECODE(status_code,'D', 'Cancelled' ,

'E', 'Error' , 'G', 'Warning',

'H','On Hold' , 'T', 'Terminating',

'M', 'No Manager' , 'X', 'Terminated',

'C', 'Normal', status_code) status_code,

actual_start_date, actual_completion_date, completion_text

FROM apps.fnd_conc_req_summary_v

WHERE request_id = &n

ORDER BY 6 DESC;

------onhand quantity------------------

  select b.inventory_item_id,b.segment1,sum(h.transaction_quantity),h.subinventory_code

from mtl_onhand_quantities h,mtl_system_items_b b

 where b.inventory_item_id = h.inventory_item_id

 and b.purchasing_enabled_flag='Y'

 and  b.organization_id =h.organization_id

 and b.organization_id =41

 and b.segment1 like 'M0127FBLK0044'

-- and h.inventory_item_id =11598

 group by b.inventory_item_id,b.segment1,h.subinventory_code;

 --------------- basepath, conc executable,..--------------------

 select fcpl.user_concurrent_program_name     

    , fcp.concurrent_program_name

  , fe.executable_name executableshortname

  , fe.execution_file_name, fe.application_id , fa.application_short_name,fa.basepath

  --, fe.execution_method_code

from fnd_concurrent_programs fcp    

     , fnd_concurrent_programs_tl fcpl

   ,fnd_executables fe,fnd_application fa

WHERE    fcp.concurrent_program_id = fcpl.concurrent_program_id

      AND fcp.executable_id = fe.executable_id

      AND fe.application_id = fa.application_id

      AND    fcpl.user_concurrent_program_name like 'Financial%'

  

  -------------Unix commands------------

  

  

  find . -name \* -exec grep "EFY" {} \; -print

----------------sales order trx num------------------

 SELECT oha.order_number, 

               ola.line_number so_line_number,

               ola.ordered_item item_name,

              ola.ordered_quantity * ola.unit_selling_price LINE_AMOUNT,

              rcta.trx_number Transaction_number, rcta.trx_date,

              rctla.line_number TRX_line_number,

               rctla.unit_selling_price unit_selling_price

  FROM oe_order_headers_all oha,

              oe_order_lines_all ola,

               ra_customer_trx_all rcta,

               ra_customer_trx_lines_all rctla

 WHERE oha.header_id = ola.header_id

   AND rcta.customer_trx_id = rctla.customer_trx_id

   AND rctla.interface_line_attribute6 = TO_CHAR (ola.line_id)

   AND rctla.interface_line_attribute1 = TO_CHAR (oha.order_number)

   AND order_number = :p_order_number;

-------------------------------------------------

Payables Transfer to General Ledger == Transfer Journal Entries to GL

Payables Accounting Process == Create Accounting

Payables Account Analysis Report == Account Analysis Report

------------------------------------------------

SELECT fu.user_id

,fu.employee_id

,fu.email_address

,fr.responsibility_name

,fa.application_name

FROM fnd_user fu

,fnd_user_resp_groups fur

,fnd_responsibility_tl fr

,fnd_application_tl fa

WHERE fu.user_id (+) = fur.user_id

AND fur.responsibility_id = fr.responsibility_id

AND fr.application_id = fa.application_id

AND fu.user_name LIKE 'SSHAPIRO';

-----------------------------------------------

Phones

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

select hcp.*  

 from hz_contact_points hcp, hz_cust_accounts hca, hz_cust_account_roles hcar 

 where hcp.owner_table_id = hcar.party_id 

 and hcar.cust_account_id = hca.cust_account_id 

 and account_number in ('4475057','4466078')

 and hcp.owner_table_name = 'HZ_PARTIES'

Sales Rep based on Sites
---------------------------------

SELECT s.name,s.salesrep_id, count(*)

FROM  hz_cust_accounts hca,

      hz_parties hp,hz_party_sites hps,

      hz_cust_acct_sites_all hcas,

      hz_cust_site_uses_all hcsu,

      ra_salesreps_all s

WHERE   hp.party_id = hca.party_id

AND     hp.party_id = hps.party_id

AND     hca.cust_account_id =  hcas.cust_account_id  

AND     hps.party_site_id = hcas.party_site_id 

AND     hcas.cust_acct_site_id = hcsu.cust_acct_site_id   

AND     s.salesrep_id =  hcsu.primary_salesrep_id

AND     hca.status='A'   

--AND     hp.status='A'

--AND     hcsu.primary_flag='Y'

--AND     hcsu.status='A'

--AND     hcsu.site_use_code ='BILL_TO'                                           

GROUP BY s.name, s.salesrep_id

ORDER BY s.name;

select account_number "Account Number"

     , hp.party_name  "Customer Name"

     , loc.address1 || 

       decode(loc.address2, null, null, ', ' || loc.address2) ||

       decode(loc.address3, null, null, ', ' || loc.address3) ||

       decode(loc.address4, null, null, ', ' || loc.address4) ||

       ' ' || loc.city || ', ' || loc.state || ' ' || loc.postal_code "Address"

     , hcp.contact_point_type || ': ' ||

       DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address

                                    , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number

                                    , 'WEB'  , hcp.url

                                    , 'Unknow contact Point Type ' || hcp.contact_point_type

             ) "How to Contact"

  from apps.hz_cust_accounts hca

     , apps.hz_parties       hp

     , apps.hz_cust_acct_sites_all cas

     , apps.hz_contact_points      hcp

     , apps.hz_party_sites         hps

     , apps.hz_locations           loc

 where hca.cust_account_id   = cas.cust_account_id

   and hca.party_id          = hp.party_id

   and hcp.owner_table_id    = cas.party_site_id

   and hcp.owner_table_name  = 'HZ_PARTY_SITES'

   and cas.party_site_id     = hps.party_site_id

   and hps.location_id       = loc.location_id  

   and hp.party_name ='PROMOTIONS' 

;

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

   --modified--

   

select account_number "Account Number"

,hp.party_name  "Customer Name"

,hcp.email_address

,(hcp.phone_area_code || ' ' || hcp.phone_number) Phone

from hz_cust_accounts hca

     ,hz_parties       hp

     ,hz_contact_points hcp

where hca.party_id=hp.party_id

and hp.party_id= hcp.owner_table_id

and hcp.owner_table_name = 'HZ_PARTIES'

and hp.party_name like 'VISION CO%'

;

---------------------------------------------------

SELECT hca.account_number Cust_No

,hp.party_name  Customer

,loc.address1 

,loc.address2

,hcp.email_address Email

,(hcp.phone_area_code || ' ' || hcp.phone_number) Phone

FROM hz_cust_accounts hca

     ,hz_parties       hp

     ,hz_cust_acct_sites_all hcas

     ,hz_contact_points      hcp

     ,hz_party_sites         hps

     ,hz_locations           loc

WHERE hca.cust_account_id = hcas.cust_account_id

and hca.party_id = hp.party_id

and hcp.owner_table_id = hcas.party_site_id

and hcp.owner_table_name = 'HZ_PARTY_SITES'

and hcas.party_site_id = hps.party_site_id

and hps.location_id = loc.location_id

and hp.party_name like 'PROMO%'

select * from hz_contact_points  where rownum<3;

select * from hz_org_contacts where rownum<3;

select account_number "Account Number"

,hp.party_name  "Customer Name"

,hp.email_address

,(hp.primary_phone_area_code || ' ' || hp.primary_phone_number) Phone

from hz_cust_accounts hca,hz_parties hp

where hca.party_id=hp.party_id

and hp.party_name like 'VISIONS EYE%';

SELECT distinct hcp.email_address as email_address

FROM hz_parties hp,

hz_contact_points hcp,

hz_cust_accounts hca,

ar_payment_schedules_all aps

WHERE hcp.owner_table_id = hp.party_id

AND hcp.owner_table_name = 'HZ_PARTIES'

AND hcp.contact_point_type = 'EMAIL'

AND hcp.status = 'A'

AND hca.party_id = hp.party_id

AND hca.cust_account_id = aps.customer_id

and hp.party_name like 'VISION C%'

--AND hca.account_number = '523557'

--AND aps.trx_number = ?

ORDER BY 1;