Help Ful queries Oracle Application (AP,GL,AR,OM,MTL)

Help Ful queries Oracle Application (AP,GL,AR,OM,MTL)

GL

1)s.o.b name,descritpion,calendar name,currency,c.o.a name for all.

select gsb.name "S.O.B NAME",

gsb.description "DESC",

gsb.period_set_name CALENDAR_NAME,

gsb.currency_code CURRENCY,

fs.id_flex_structure_code

from

gl_sets_of_books gsb,

fnd_id_flex_structures FS

WHERE

gsb.chart_of_accounts_id=fs.id_flex_num;

AND gsb.name like '468%';

2)calendar name,desc,period name,begin of period,end of period,period type,period type desc for all.

SELECT gps.period_set_name CAL_NAME,

gps.description CAL_DESC,

GP.PERIOD_NAME ,GP.START_DATE,GP.END_DATE,

GPT.PERIOD_TYPE,gpt.description PERIOD_TYPE_DESC

FROM

GL_PERIOD_SETS GPS,

GL_PERIODS GP,

GL_PERIOD_TYPES GPT

WHERE

GPS.PERIOD_SET_NAME=GP.PERIOD_SET_NAME

AND GP.PERIOD_TYPE=GPT.PERIOD_TYPE

AND gps.period_set_name LIKE '468%';

3)s.o.b name,period name,status for all open periods for ur s.o.b

SELECT SOB.NAME,

PSTAT.PERIOD_NAME,

PSTAT.CLOSING_STATUS,

GLK.MEANING

FROM GL_PERIOD_STATUSES PSTAT,

GL_SETS_OF_BOOKS SOB,

GL_LOOKUPS GLK

WHERE SOB.SET_OF_BOOKS_ID=pstat.set_of_books_id

AND PSTAT.CLOSING_STATUS LIKE 'O'

AND GLK.LOOKUP_CODE=pstat.closing_status

AND GLK.LOOKup_type='CLOSING_STATUS'

and sob.name like '%464%';

4)c.o.a for accounting flex field

structure code,structure type,structure desc

SELECT fifs.id_flex_structure_code "COA Code",

tl.id_flex_structure_name "COA Name",

tl.description

FROM fnd_id_flexs fif,

fnd_id_flex_structures fifs,

fnd_id_flex_structures_tl tl

WHERE fif.id_flex_code = fifs.id_flex_code

AND fifs.application_id = fifs.application_id

AND fifs.id_flex_code =tl.id_flex_code

AND fifs.id_flex_num =tl.id_flex_num

AND fif.id_flex_name like 'Accounting Flexfield'

AND id_flex_structure_code like '%464%' ;

5)value set lo

segment name,value set and values

5)

select fifs.Segment_name,

ffvs.flex_value_set_name,

ffv.flex_value

from fnd_id_flex_segments fifs,

fnd_flex_value_sets ffvs,

fnd_flex_values_vl ffv

where 1=1

and fifs.flex_value_set_id = ffvs.flex_value_set_id

and id_flex_num=52569;

6)journal name,desc,journal source,j.category,j.period,j.curr for different dates.

6)

SELECT name,

je_category,

je_source,

period_name,

currency_code,

description

FROM gl_je_headers

WHERE je_source='Payables';

7)display the jour name,sob id,j.batch_name,j.source,j.category,period name,status for all unposted journals.

7)

select gjh.name,

gjh.set_of_books_id,

gjb.name,

gjh.je_source,

gjh.je_category,

gjh.period_name,

gjh.status

from gl_je_headers gjh,

gl_je_batches gjb

where gjh.je_batch_id = gjb.je_batch_id

and upper(gjh.status) = 'U';

8)j.name,j.line account,amount debited,credited,j.line desc

8)

select gjh.name,

gcc.segment1 ||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4 "Account Details",

gjl.accounted_dr,

gjl.accounted_cr,

gjl.description

from gl_je_headers gjh,

gl_je_lines gjl,

gl_code_combinations gcc

where 1=1

and gjh.je_header_id = gjl.je_header_id

and gjl.code_combination_id = gcc.code_combination_id;

Inventory

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

1. Pull the details of item lot numbers.

1).

select msib.segment1 "Item Name",

msib.description,

ml.meaning,

msib.auto_lot_alpha_prefix "Lot Prefix",

msib.start_auto_lot_number "Lot Starting Number",

mln.lot_number

from mtl_system_items_b msib,

mtl_lot_numbers mln,

mfg_lookups ml

where msib.organization_id = mln.organization_id

and msib.inventory_item_id = mln.inventory_item_id

and msib.lot_control_code = ml.lookup_code

and ml.lookup_type like 'MTL_LOT_CONTROL'

and msib.segment1 like 'sample_464';

--and msib.segment1 like 'Lot control item';

2. Display transaction quantity at Sub-Inventory level.

2).

select msib.segment1,

moq.subinventory_code,

sum(moq.transaction_quantity)

from mtl_system_items_b msib,

mtl_onhand_quantities moq --mtl_material_transactions

where msib.inventory_item_id = moq.inventory_item_id

and msib.organization_id = moq.organization_id

and msib.inventory_item_id = 13128

group by msib.segment1,

moq.subinventory_code,

moq.organization_id;

3. Display details of Items, Organization and controls on that item.

3).

select item.segment1,

org.organization_code,

l1.meaning,

l2.meaning,

l3.meaning

from mtl_system_items_b item,

mtl_parameters org,

mfg_lookups l1,

mfg_lookups l2,

mfg_lookups l3

where 1=1

and org.organization_id=item.organization_id

and l1.lookup_code=item.lot_control_code

and l1.lookup_type='MTL_LOT_CONTROL'

and l2.Lookup_Code=Item.serial_number_control_code

and l2.lookup_type='MTL_SERIAL_NUMBER'

and l3.Lookup_Code=item.shelf_life_code

and l3.lookup_type='MTL_SHELF_LIFE'

and item.segment1 like 'sample_464';

4. Query to list out main assembly item and corresponding Components

4).

select msib.segment1 "Component Name" ,

msib1.segment1 "Sub-Components Name"

from mtl_system_items_b msib,

mtl_system_items_b msib1,

bom_bill_of_materials bbom,

bom_inventory_components bic

where bbom.organization_id = msib.organization_id

and bbom.assembly_item_id = msib.inventory_item_id

and bbom.bill_sequence_id = bic.bill_sequence_id

and bic.component_item_id = msib1.inventory_item_id

and msib.segment1 like 'NokiaMobile'

and bbom.alternate_bom_designator is null

and msib1.organization_id = msib.organization_id;

Components

5. Item NO, Organization, Cost Type Name and material Cost of Item.

5).

select msib.segment1 "Item Name",

mp.organization_code,

cict.cost_type,

cic.material_cost

from mtl_system_items_b msib,

mtl_parameters mp,

cst_item_costs cic,

cst_cost_types cict

where 1-1=0

and msib.organization_id = mp.organization_id

and msib.organization_id = cic.organization_id

and msib.inventory_item_id = cic.inventory_item_id

and cic.cost_type_id = cict.cost_type_id

and msib.segment1 like 'NokiaMobile'

and cic.organization_id = 207;

6. Display details of all Routing Information, if Standard Operation display Standard Operation Name.

6).

select msib.segment1,

mp.organization_code,

msib.description,

bd.department_class_code,

bd.department_code,

br.resource_code,

bos.operation_description,

bso.operation_code

from mtl_system_items_b msib,

mtl_parameters mp,

bom_operational_routings bor,

bom_operation_sequences bos,

bom_standard_operations bso,

bom_departments bd,

bom_department_classes bdc,

bom_operation_resources borE,

bom_resources br

where 1=1

and msib.organization_id = mp.organization_id

and msib.inventory_item_id = bor.assembly_item_id

and msib.organization_id = bor.organization_id

and bor.routing_sequence_id = bos.routing_sequence_id

and bos.standard_operation_id = bso.standard_operation_id

and bos.department_id = bd.department_id

and bd.organization_id = bdc.organization_id

and bd.department_class_code = bdc.department_class_code

and bos.operation_sequence_id = bore.operation_sequence_id

and bore.resource_id = br.resource_id

and bd.organization_id = br.organization_id

and msib.segment1 like 'NokiaMobile'

and msib.organization_id = 207

and bor.alternate_routing_designator is null;

7.Write a query to find the Item name, Organization, Revision, Inventory Item Id of a item based on the Item

name(Inventory).

7).

select msib.segment1 "Item Name",

mp.organization_code,

mir.revision_label

from mtl_system_items_b msib,

mtl_item_revisions mir,

mtl_parameters mp

where 1-1=0

and msib.organization_id = mp.organization_id

and msib.inventory_item_id = mir.inventory_item_id

and msib.organization_id = mir.organization_id

and msib.segment1 like 'NokiaMobile';

Purchasing

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

1)QUERY TO BE PREAPRED TO LIST ALL THE REQUISTIONS WITH STATUS

REQNO,DOF REQ,APPROVED BY,AMOUNT OF REQ,STATUS,DESCRIPTION

1).

select h.SEGMENT1,h.CREATION_DATE,h.AUTHORIZATION_STATUS,

h.DESCRIPTION,l.TO_PERSON_ID,AH.ACTION_CODE,

E.FULL_NAME, L.quantity*l.unit_price "req.amt"

from po_requisition_headers_all h,

po_requisition_lines_all l,

po_action_history AH,

Hr_employees E

where H.REQUISITION_HEADER_ID = L.REQUISITION_HEADER_ID

AND H.REQUISITION_HEADER_ID = AH.OBJECT_ID

AND AH.EMPLOYEE_ID = E.EMPLOYEE_ID

AND H.SEGMENT1 = TO_CHAR(6076);

2)QUERY TO PULL THE DEATILS OF RECEIVING TRANSACTION

INVENTORY_CODE,INVEN NAME,ITEM NO,ORDERD QUANTITY,RECEIVED QUANTITY,VENDOR NAME,VENDOR ADD AND RECEIPT NO

2)

select msi.secondary_inventory_name, msi.description,

msib.segment1, pla.quantity,sum(rsl.quantity_received),

pv.vendor_name,pvsa.address_line1||' '||pvsa.address_line2||' '||

pvsa.city||' '||pvsa.state "vendor address",

ft.territory_short_name,rsh.receipt_num

from po_headers_all pha,

po_lines_all pla,

mtl_system_items_b msib,

rcv_shipment_headers rsh,

rcv_shipment_lines rsl,

mtl_secondary_inventories msi,

po_vendors pv,

po_vendor_sites_all pvsa,

fnd_territories_tl ft

where pv.vendor_name='Supriya'

--and pha.segment1 = '6076'

and pha.po_header_id = pla.po_header_id

and pla.item_id = msib.inventory_item_id

and pha.org_id = msib.organization_id

and rsh.shipment_header_id = rsl.shipment_header_id

and pha.po_header_id = rsl.po_header_id

and pla.po_line_id = rsl.po_line_id

and rsl.to_organization_id = msi.organization_id

and rsl.to_subinventory = msi.secondary_inventory_name

and pha.vendor_id = pv.vendor_id

and pha.vendor_site_id = pvsa.vendor_site_id

and pvsa.country = ft.territory_code

group by msi.secondary_inventory_name, msi.description,

msib.segment1, pla.quantity,pv.vendor_name,

pvsa.address_line1,

pvsa.address_line2,pvsa.city,pvsa.state,

ft.territory_short_name,rsh.receipt_num;

3)QUERY TO PULL THE DETAILS OF PURCHASE ORDERS

PO NO,PO TYPE,PO CREATOR NAME,BUYER,APPROVER,STATUS,ITEM NO,ITEM DESCRIPTION,PO AMOUNT

3).

select pha.segment1, plct.description, hpc.party_name "po_creator",

hpa.party_name "po_approver",plcs.displayed_field,

plcs.description,msib.segment1,msib.description,

(pla.unit_price * pla.quantity)

from po_headers_all pha,

hz_parties hpc,

po_action_history pah,

po_lookup_codes plct,

hz_parties hpa,

po_lookup_codes plcs,

po_lines_all pla, mtl_system_items_b msib

where 1=1

and pha.segment1 = '4947'

and pha.agent_id = hpc.person_identifier

and pha.po_header_id = pah.object_id

and pah.action_code = 'APPROVE'

and plct.lookup_type = 'PO TYPE'

and plct.lookup_code = pah.object_sub_type_code

and pah.employee_id = hpa.person_identifier

and plcs.lookup_type = 'AUTHORIZATION STATUS'

and pha.authorization_status = plcs.lookup_code

and pha.po_header_id = pla.po_header_id

and pla.item_id = msib.inventory_item_id

and pha.org_id = msib.organization_id;

4)QUERY TO B PREPARED TO LIST THE DEATILS OF SUPPLIER,SUPPLIER DETAILS ALONG WITH THE NO.OF.PO PLACED DURING THE LAST_FINANCIAL_YEAR(THIS JUNE TO LAST_JUNE)

4).

select pv.vendor_name,

pvsa.address_line1||' '||pvsa.address_line2||' '||

pvsa.city||' '||pvsa.state "vendor address",ft.territory_short_name ,

COUNT(pv.vendor_name)

from po_vendors pv,

po_headers_all pha,

po_vendor_sites_all pvsa,

fnd_territories_tl ft

where 1=1

and pv.vendor_name = 'Supriya'

and pvsa.vendor_id = pv.vendor_id

and pha.vendor_id = pv.vendor_id

and pha.vendor_site_id = pvsa.vendor_site_id

and pvsa.country = ft.territory_code

and pha.org_id = 204

GROUP BY pvsa.address_line1,pvsa.address_line2,pv.vendor_name,

pvsa.city,pvsa.state,ft.territory_short_name;

5)COMPLETE PO FROM REQNO TO RECEIPTS

REQ NO,ITEM NO,REQ QUANTITY,ORG,TRANS QUANTITY,SUBINV CODE,DISTRIBUTION ACCOUNT,PO NO,PO TYPE,PO QUANTITY,VENDOR ID,VENDOR NAME,VENDOR ADD

5).

SELECT msib.segment1,prh.segment1 "Req Num",plc.description,

pha.segment1 "PO Num", msi.secondary_inventory_name,pv.vendor_name,

pvs.ADDRESS_LINE1||' '|| pvs.CITY, pla.quantity,prl.quantity,

sum(pla.quantity*pla.unit_price),sum(prl.quantity*prl.unit_price),

sum(rcv2.quantity) "quantity delivered"

,sum(rcvl.quantity_shipped) "quantity shipped"

FROM po_headers_all pha,

po_lines_all pla,

po_distributions_all pda,

po_req_distributions_all prd,

po_requisition_lines_all prl,

po_requisition_headers_all prh,

rcv_transactions rcv2,

rcv_shipment_headers rcvh,

rcv_shipment_lines rcvl,

po_vendors pv,

po_vendor_sites_all pvs,

mtl_system_items_b msib,

mtl_secondary_inventories msi,

po_lookup_codes plc

WHERE pv.vendor_name='Supriya'

AND pha.po_header_id = pda.po_header_id

AND pda.req_distribution_id = prd.distribution_id

AND prd.requisition_line_id = prl.requisition_line_id

AND prl.requisition_header_id = prh.requisition_header_id

and pha.po_header_id=pla.po_header_id

and pha.org_id=204

--and pha.segment1='&enter'

AND pha.po_header_id =rcv2.po_header_id

and pha.po_header_id =pla.po_header_id

and pla.po_line_id=rcvl.po_line_id

and pla.po_line_id=rcv2.po_line_id

AND rcv2.transaction_type ='DELIVER'

AND rcvh.shipment_header_id=rcv2.shipment_header_id

and pha.vendor_id=pv.vendor_id

and pha.vendor_site_id=pvs.vendor_site_id

and msib.inventory_item_id=pla.item_id

and msib.inventory_item_id=rcvl.item_id

and msib.organization_id=rcvl.to_organization_id

and plc.lookup_type='PO TYPE'

and pha.type_lookup_code=plc.lookup_code

--and msi.organization_id=rcvl.to_organization_id

--and pha.org_id=204

and msi.secondary_inventory_name=rcv2.subinventory

and msib.organization_id=msi.organization_id

group by msib.segment1,prh.segment1,plc.description,

pha.segment1, msi.secondary_inventory_name,pv.vendor_name,

pvs.ADDRESS_LINE1||' '|| pvs.CITY, pla.quantity,prl.quantity;

Payables

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

1)list all the invoices and supplier information

supplier_no,supp name,supplier site,invoice no,invoice curr,amount

1).

select pv.segment1 "Supplier Number",

pv.vendor_name "Supplier Name",

pvsa.vendor_site_id "Supplier Site",

aia.invoice_num "Invoice Number",

aia.invoice_currency_code "Currency Code",

aia.invoice_amount

from ap_invoices_all aia,

po_vendors pv,

po_vendor_sites_all pvsa

where 1=1

and pv.vendor_id = pvsa.vendor_id

and aia.vendor_id = pv.vendor_id

and aia.vendor_site_id = pvsa.vendor_site_id

and aia.org_id = 204

and aia.invoice_num = 'HYD01';

2)list all the open invoices showing balances(not paid and partially paid)

invoice no,invoice amount,supplier no,supplier name,date of invoice creation,status(paid or not) and balance

2).

select aia.invoice_num

,aia.invoice_amount

,pv.vendor_id

,pv.vendor_name

,aia.invoice_date --New

,alc.displayed_field

,apsa.amount_remaining

from ap_invoices_all aia,

po_vendors pv,

ap_payment_schedules_all apsa,

ap_lookup_codes alc

where 1=1

and aia.vendor_id=pv.vendor_id

and aia.invoice_id = apsa.invoice_id

and apsa.payment_status_flag = alc.lookup_code

and aia.org_id = 204

and alc.lookup_type='INVOICE PAYMENT STATUS'

and alc.displayed_field not in ('Yes')

and aia.invoice_num = 'HYD01';

3)list all the invoices on which the payment has been padi

invoice no,do of invoice creation,do of payment of invoice,supplier name,payment document no

3).

select aia.invoice_num,

aia.invoice_date,

aipa.creation_date,

aca.vendor_name,

aca.check_number

from ap_invoices_all aia,

ap_invoice_payments_all aipa,

ap_checks_all aca

where 1=1

and aia.invoice_id = aipa.invoice_id

and aipa.check_id = aca.check_id

and aia.org_id = 204

and aia.invoice_num = 'HYD01';

4)invoice no,do of invoice creation,do of payment of invoice,supplier name,distribution account

4).

select aia.invoice_num,

aia.invoice_date,

aia.creation_date,

aida.amount,

aia.invoice_id,

aia.invoice_amount,

aipa.creation_date payment_date,

pv.vendor_name,

gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5 "Distribution A/C"

from ap_invoices_all aia,

po_vendors pv,

ap_invoice_distributions_all aida,

gl_code_combinations gcc,

ap_invoice_payments_all aipa

where 1=1

and aia.vendor_id = pv.vendor_id

and aia.invoice_id = aida.invoice_id

and aida.dist_code_combination_id = gcc.code_combination_id

and aipa.invoice_id = aia.invoice_id

and aipa.invoice_id = 10250

and aia.org_id = 204

--and pv.vendor_id = 1937

order by aipa.invoice_id;

OM

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

1)develop a query which lists the orders which r on hold

i)order date,order no,order type(name),order item,date of hold,reason of hold,hold by,days of hold.

ii)for same query the no of days of hold should be a parameter

1).

SELECT h.order_number,

ot.name,

l.ordered_item,

ohsa.creation_date,

ohsa.hold_comment,

fu.user_name,

TRUNC(sysdate)-TRUNC(ohsa.creation_date)

FROM oe_order_headers_all h,

oe_order_lines_all l,

oe_transaction_types_tl ot,

oe_order_holds_all ooha,

oe_hold_sources_all ohsa,

fnd_user fu

WHERE h.header_id = l.header_id

AND h.order_type_id = ot.transaction_type_id

AND h.header_id = ooha.header_id

AND ooha.hold_source_id = ohsa.hold_source_id

AND ohsa.created_by = fu.user_id

AND order_number =57265;

2)develop a query to list all the unbooked sales orders

order no,order item,order quantity,order date,order status

2).

select oha.order_number,

ola.ordered_item,

ola.ordered_quantity,

oha.ordered_date,

oha.flow_status_code "Status"

from oe_order_headers_all oha,

oe_order_lines_all ola

where 1=1

and oha.header_id = ola.header_id

and oha.org_id = 204

and ola.flow_status_code = 'ENTERED';

3)list all the orders which r backordered

order no,customer no,cust name,order type(name),status,date of order,item no,description

3).

select oha.order_number,

hp.party_name,

hca.account_number,

ott.name "Order Type",

oha.flow_status_code "Order Status",

ola.flow_status_code "Line Status",

oha.ordered_date,

ola.ordered_item,

wdd.item_description

from oe_order_headers_all oha,

oe_order_lines_all ola,

wsh_delivery_details wdd,

fnd_lookup_values flv,

oe_transaction_types_tl ott,

hz_parties hp,

hz_cust_accounts hca

where 1=1

and oha.header_id = ola.header_id

and ott.transaction_type_id = oha.ORDER_TYPE_ID

and wdd.source_header_id = ola.header_id

and oha.sold_to_org_id = hca.cust_account_id(+)

and hp.party_id = hca.party_id

and flv.lookup_code = wdd.released_status

and wdd.released_status = 'B'

and oha.org_id = 204

and oha.order_number = 57271

and flv.lookup_type = 'PICK_STATUS';

4)develop a query to list all the orders which r ready for shipping

4).

select oha.order_number,

hp.party_name,

oha.ordered_date,

oha.batch_id,

ola.flow_status_code

from oe_order_headers_all oha,

oe_order_lines_all ola,

wsh_delivery_details wdd,

hz_parties hp,

hz_cust_accounts hca

where 1=1

and oha.header_id = ola.header_id

and wdd.source_header_id = ola.header_id

and wdd.source_line_id = ola.line_id

and hca.cust_account_id = oha.sold_to_org_id

and hp.party_id = hca.party_id

and oha.org_id = 204

and ola.flow_status_code = 'AWAITING_SHIPPING';

Receivables

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

1)develop a query to list customer details

cust name,cust no,profile class,primary bill_to_address(country,address,city,state,postal code),primary ship_to_address(country,address,city,state,po)

1)

select hp1.party_name,

hp1.party_id,

hca.account_number,

hcpc.name,

hcsua.site_use_code,

hl.country||' '||hl.address1||' '||hl.city||' '||hl.state||' '||hl.postal_code "Address"

from hz_parties hp1

, hz_cust_accounts hca

, HZ_PARTY_SITES hps

, hz_cust_profile_classes hcpc

, hz_customer_profiles hcp

, hz_locations hl

, hz_cust_acct_sites_all hcasa

, hz_cust_site_uses_all hcsua

where 1=1

and hca.account_number=3896

and hcp.profile_class_id=hcpc.profile_class_id(+)

and hca.cust_account_id=hcp.cust_account_id

and hp1.party_id=hca.party_id

--and hca.cust_account_id=hcasa.cust_account_id

and hps.party_site_id=hcasa.party_site_id

and hcasa.cust_acct_site_id=hcsua.cust_acct_site_id

and hp1.party_id=hps.party_id

and hl.location_id=hps.location_id

and hcsua.primary_flag='Y'

and hcp.site_use_id is null

and hcasa.org_id=204

;

2)to extract customer invoice details,cust name,total invoice relevant to customer

cust no,cust name,date of invoice generated,amount of invoice,amount of invoice last paid by the customer,credit limit,balance amount unpaid

2)

select hca.account_number

, hp.party_name

, rcta.trx_date

, (select sum(rctla.extended_amount) from ra_customer_trx_lines_all rctla

where rcta.customer_trx_id = rctla.customer_trx_id) "INVOICE AMOUNT"

, hcpa.overall_credit_limit

, apsa.amount_due_remaining

, acra.amount

from hz_parties hp

, hz_cust_accounts hca

, hz_customer_profiles hcp

, hz_cust_profile_amts hcpa

, ra_customer_trx_all rcta

--, ra_customer_trx_lines_all rctla

, ar_cash_receipts_all acra

, ar_receivable_applications_all araa

, ar_payment_schedules_all apsa

where 1=1

and hp.party_id = hca.party_id

and hca.cust_account_id = hcp.cust_account_id

and hcp.site_use_id is null

and hcpa.cust_account_profile_id = hcp.cust_account_profile_id

and hcpa.currency_code = 'USD'

and rcta.sold_to_customer_id = hca.cust_account_id

and rcta.org_id = 204

--and rcta.customer_trx_id = rctla.customer_trx_id

and rcta.customer_trx_id = apsa.customer_trx_id

and araa.applied_customer_trx_id = apsa.customer_trx_id

and acra.cash_receipt_id = araa.cash_receipt_id

and rcta.trx_number = '11792'

and acra.cash_receipt_id = (select max(cash_receipt_id) from ar_receivable_applications_all araa_2

where araa_2.applied_customer_trx_id = apsa.customer_trx_id)

;

3)to list all the invoices whicha r pending to b paid for more than 60 days from sysdate

cust no,cust name,invoice no,date of invoice,amount,period of invoice pending for 30-60 days,period of invoice pending for 60-120 days,period of invoice pending for more than 120 days

3)

select hca.account_number

, hp.party_name

, rcta.trx_number

, rcta.trx_date

, sum(rctla.extended_amount)

, case when trunc(sysdate-rcta.trx_date) between 30 and 60

then rcta.trx_number

end "Pending for 30 - 60 days"

, case when trunc(sysdate-rcta.trx_date) between 60 and 120

then rcta.trx_number

end "Pending for 60 - 120 days"

, case when trunc(sysdate-rcta.trx_date) > 120

then rcta.trx_number

end "Pending for more than 120 days"

from hz_parties hp

, hz_cust_accounts hca

, ra_customer_trx_all rcta

, ra_customer_trx_lines_all rctla

where hp.party_id = hca.party_id

and rcta.org_id = 204

and hca.cust_account_id = rcta.sold_to_customer_id

and rcta.customer_trx_id = rctla.customer_trx_id

and hca.account_number = 3896

group by hca.account_number

, hp.party_name

, rcta.trx_number

, rcta.trx_date

;

-

4)to list all the invoices paid during past 6 months

cust no,cust name,invoice no,receipt no,date of invoive,date of payment,amount

4)

SELECT hca.account_number

, hp.party_name

, rcta.trx_number

, acra.receipt_number

, rcta.trx_date

, acra.creation_date

, acra.amount

FROM hz_parties hp

, hz_cust_accounts hca

, ra_customer_trx_all rcta

--, ra_customer_trx_lines_all rctla

, ar_cash_receipts_all acra

, ar_payment_schedules_all apsa

, ar_receivable_applications_all araa

, ar_lookups al

where 1=1

and hp.party_id = hca.party_id

and rcta.sold_to_customer_id = hca.cust_account_id

and rcta.org_id = 204

--and rcta.customer_trx_id = rctla.customer_trx_id

and rcta.customer_trx_id = apsa.customer_trx_id

and araa.applied_customer_trx_id = apsa.customer_trx_id

and acra.cash_receipt_id = araa.cash_receipt_id

and acra.status = al.lookup_code

and al.lookup_type = 'PAYMENT_TYPE'

and rcta.trx_number = '11792'

and trunc(months_between(sysdate,araa.creation_date)) <= 6;