Invoice amounts in Receivables

QUERY FOR GETTING THE 2ND MAXIMUM INVOICE AMOUNT
===============================================
select max(amt) secondmax  from (select sum(l.EXTENDED_AMOUNT) amt,h.TRX_NUMBER no from ra_customer_trx_all h,ra_customer_trx_lines_all l
where h.customer_trx_id=l.CUSTOMER_TRX_ID
and h.CREATION_DATE like sysdate
GROUP BY h.TRX_NUMBER
ORDER BY 1 desc)
where amt <
(select max(amt) a from (select sum(l.EXTENDED_AMOUNT) amt,h.TRX_NUMBER no from ra_customer_trx_all h,ra_customer_trx_lines_all l
where h.customer_trx_id=l.CUSTOMER_TRX_ID
and h.CREATION_DATE like sysdate
GROUP BY h.TRX_NUMBER
ORDER BY 1 desc));

QUERY FOR GETTING THE MAXIMUM  INVOICE AMOUNT
=============================================
(select max(amt) a from (select sum(l.EXTENDED_AMOUNT) amt,h.TRX_NUMBER no from ra_customer_trx_all h,ra_customer_trx_lines_all l
where h.customer_trx_id=l.CUSTOMER_TRX_ID
and h.CREATION_DATE like sysdate
GROUP BY h.TRX_NUMBER
ORDER BY 1 desc));


QUERY FOR GETTING THE MAXIMUM INVOICE AMOUNT AND ALL  IN DESCENDING ORDER
============================================================
select sum(l.EXTENDED_AMOUNT) amt,h.TRX_NUMBER no from ra_customer_trx_all h,ra_customer_trx_lines_all l
where h.customer_trx_id=l.CUSTOMER_TRX_ID
and h.CREATION_DATE like sysdate
GROUP BY h.TRX_NUMBER
ORDER BY 1 desc;

Query to find out responsibility and request group for concurrent program

SELECT DISTINCT
  FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
  APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
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 FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE :conc_prg_name
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US';

Query For Getting The Sub Inventories for Inventory Item

select mi.secondary_inventory_name,mi.DESCRIPTION,ms.INVENTORY_ITEM_ID from mtl_secondary_inventories mi,MTL_SYSTEM_ITEMS_B ms
WHERE secondary_inventory_name IN( 'LA_FGI_L','LA_FGI_PLT','LA_FGI_U')
and mi.ORGANIZATION_ID=ms.ORGANIZATION_ID
and ms.INVENTORY_ITEM_ID='5245705'
and mi.organization_id = 4;

Relation between AR invoice and Sales Order

SELECT oha.order_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 oha.cust_po_number = :cust_po_number

Query To Get The Ship To Address By Order Number in Oracle Apps

SELECT DISTINCT LOC.ADDRESS1,LOC.ADDRESS2,HCSUA.LOCATION,H.ORDER_NUMBER,H.CUST_PO_NUMBER,HP.PARTY_NAME,HP.PARTY_NUMBER FROM 

OE_ORDER_HEADERS_ALL H,

HZ_LOCATIONS LOC,

HZ_PARTY_SITES HPS,

HZ_CUST_ACCT_SITES_ALL HCASA,

HZ_CUST_SITE_USES_ALL HCSUA,

HZ_PARTIES HP

WHERE H.SOLD_TO_ORG_ID=HCASA.CUST_ACCOUNT_ID

AND HCSUA.SITE_USE_ID=H.SHIP_TO_ORG_ID

AND LOC.LOCATION_ID=HPS.LOCATION_ID

AND HPS.PARTY_SITE_ID=HCASA.PARTY_SITE_ID

AND HCSUA.CUST_ACCT_SITE_ID=HCASA.CUST_ACCT_SITE_ID

AND HP.PARTY_ID=H.SOLD_TO_ORG_ID

AND H.ORDER_NUMBER='14565';

To get the request details by Request id

SELECT

user_concurrent_program_name,

responsibility_name,

request_date,

argument_text,

request_id,

phase_code,

status_code

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 request_id='27775711'

ORDER BY REQUEST_DATE DESC;

Open or Close Periods in R12

Open or Close Periods in R12

Purchasing Periods

1. Navigate to Purchasing SuperUser->Setup->Financials->Accounting->Control Purchasing Periods , then enter the Fiscal year and click Go button.

(If it prompts for selecting Operating Unit,select it and click Go button.)

2. Then open the periods by changing the Period Status and click on Save button.

Payables Period

1. Navigate to Payables Super User or Payable Manager->Accounting->Control Payables Periods , then open the periods by changing the Period Status and click on Save button.

General Ledger Periods

1. Navigate to General Ledger Super User->Setup->Open/Close, then the Ledger and click find.

2. Now open the required periods by clicking “Open Periods” button,select the required period as Target Period and click Open button.

Inventory Periods

1. Navigate to Inventory SuperUser->Accounting Close Cycle->Inventory Accounting Periods and select the Inventory Org from LOV.

2. Select the period to be opened and click on Change Status button.