Oracle Application Credit Memo R12

 Oracle Application  R12 credit memo:

Credit memo is documents which is issued by seller to buyer against return or exchange, billing error or price adjustment.

In oracle application, when seller issues credit memo against the invoices of the buyer, the liablity of buyer to pay bills is removed.

We can create credit memo in front end  by going to AR->Cash Receipt as credit memo.
We need to make sure the right amount , buyer name and billing address so that credit memo gets applied to wrigth place.

Technically we can apply credit memo by using following oracle supplied credit memo.

The following program is for demo purpose only.The program fetches the late fees, check if payment made withinn xxx days .If payment made then it applies credit memo for those late fees invoices.

declare 

v_return_status varchar2 (1);

p_count number;

v_msg_count number;

v_msg_data varchar2 (2000);

v_request_id number;

 

l_cm_lines_tbl arw_cmreq_cover.cm_line_tbl_type_cover;

l_customer_trx_id number;

cm_trx_id number;

v_interface_header_rec arw_cmreq_cover.pq_interface_rec_type;

ind number;

l_trx_number varchar2 (30):= 0;

l_org_id number:= 0;

 

v_trx_date date;

l_payment_flag boolean;

/*the cursor fetches all of the late fees type invoice*/

cursor c_fetch_late_fees ( p_org_id number default null, p_cust_name varchar2 default null, p_sales_order_no varchar2 default null, p_trx_number varchar default null ) is 

select

   rct.customer_trx_id,

   rct.trx_number invoice_number,

   rct.recurred_from_trx_number,

   rct.reason_code,

   arps.amount_due_original balance_original,

   arps.amount_due_remaining balance_due_remaining,

   hp.party_name bill_to_customer,

   case

      rctt.type 

      when

         'cb' 

      then

         'chargeback' 

      when

         'cm' 

      then

         'credit memo' 

      when

         'dm' 

      then

         'debit memo' 

      when

         'dep'

      then

         'deposit' 

      when

         'guar'

      then

         'guarantee' 

      when

         'inv' 

      then

         'invoice' 

      when

         'pmt' 

      then

         'receipt' 

      else

         'invoice' 

   end

   invoice_class, rct.invoice_currency_code currency, rct.trx_date inv_date, rctd.gl_date gl_date, 

   (

      select

         name 

      from

         apps.ra_terms rat 

      where

         rat.term_id = rct.term_id 

   )

   terms,

   rctt.name order_type,

   rct.attribute15 installment_no,

   rct.attribute6,

   rct.attribute7,

   rct.attribute8,

   rct.attribute9,

   rct.attribute14,

   rct.attribute15,

   rct.ct_reference,

   owtt.product_type 

from

   ra_customer_trx_all rct,

   ra_customer_trx_lines_all rctl,

   ra_cust_trx_line_gl_dist_all rctd,

   hz_parties hp,

   hz_cust_accounts_all hca,

   ra_cust_trx_types_all rctt,

   hr_operating_units hou,

   ar_payment_schedules_all arps 

where

   rct.customer_trx_id = rctd.customer_trx_id 

   and rct.customer_trx_id = rctl.customer_trx_id 

   and rctl.customer_trx_line_id = rctd.customer_trx_line_id 

   and rct.bill_to_customer_id = hca.cust_account_id 

   and arps.status = 'op' 

   and arps.class = 'inv' 

   and okch.bill_to_site_use_id = rct.bill_to_site_use_id 

   and hp.party_id = hca.party_id 

   and rct.cust_trx_type_id = rctt.cust_trx_type_id 

   and rct.org_id = rctt.org_id 

   and rct.org_id = hou.organization_id 

   and arps.customer_trx_id( + ) = rct.customer_trx_id 

   and rct.org_id = nvl (p_org_id, rct.org_id) 

   and rct.trx_number = nvl (p_trx_number, rct.trx_number) 

   and hp.party_name = nvl (p_cust_name, hp.party_name) 

   and nvl (rct.ct_reference, 'xx') = nvl (nvl (p_sales_order_no, rct.ct_reference), 'xx') 

group by

   rct.trx_number,

   arps.amount_due_original,

   arps.amount_due_remaining,

   hp.party_name,

   rctt.type,

   rct.invoice_currency_code,

   rct.trx_date,

   rctd.gl_date,

   rct.term_id,

   rctt.name,

   rct.attribute6,

   rct.attribute7,

   rct.attribute8,

   rct.attribute9,

   rct.attribute14,

   rct.attribute15,

   rct.ct_reference,

   rct.reason_code,

   rct.recurred_from_trx_number,

   rct.customer_trx_id,

   recurred_from_trx_number,

   product_type 

order by

   inv_date desc ;

/*the cursor fetches all of the invoices, for which late fees has been generated */

cursor c_fetch_related_invoice (p_org_id varchar2, p_trx_date date, p_ct_reference varchar2) is 

select

   trx_number,

   trx_date,

   interface_header_attribute9,

   ct_reference,

   amount_due_remaining,

   set_of_books_id 

from

   (

      select

         rct.trx_number,

         rct.cust_trx_type_id,

         rct.trx_date,

         rct.set_of_books_id,

         rct.batch_id,

         rct.batch_source_id,

         rct.bill_to_customer_id,

         rct.bill_to_site_use_id,

         rct.term_id,

         rct.invoice_currency_code,

         rct.attribute6,

         rct.attribute7,

         rct.attribute8,

         rct.attribute9,

         rct.attribute11,

         rct.attribute14,

         rct.attribute15,

         rct.interface_header_attribute6,

         rct.interface_header_attribute7,

         rct.interface_header_context,

         rct.interface_header_attribute14,

         rct.interface_header_attribute9,

         rct.status_trx,

         rct.org_id,

         rctl.amount_due_original,

         rctl.amount_due_remaining,

         rctl.amount_includes_tax_flag,

         rct.ct_reference 

      from

         ra_customer_trx_all rct,

         ra_customer_trx_lines_all rctl,

         ar_payment_schedules_all aprs 

      where

         rct.customer_trx_id = rctl.customer_trx_id 

         and rct.customer_trx_id = aprs.customer_trx_id 

         and rct.trx_date = p_trx_date 

         and rct.batch_source_id = xx 

         and rct.cust_trx_type_id = xx 

         and rct.invoice_currency_code = xx 

         and aprs.class = 'inv' 

      order by

         trx_date desc

   )

order by

   trx_date desc ;

/*the cursor fetches if invoice has been paid or not */

cursor check_invoice_paid_yn (p_trx_number varchar2, p_ct_refernce varchar2) is 

select

   rct.trx_number invoice_no,

   rct.ct_reference 

from

   ar_receivable_applications_all ara,

   ar_cash_receipts_all acr,

   ra_customer_trx_all rct 

where

   ara.status = 'app' 

   and ara.cash_receipt_id = acr.cash_receipt_id 

   and ara.applied_customer_trx_id = rct.customer_trx_id 

   and rct.trx_number in p_trx_number 

   and rct.ct_reference = p_ct_refernce ;

lc check_invoice_paid_yn % rowtype;

cursor c_inv (p_trx_number varchar2, p_ct_refernce varchar2 ) is 

select

   rct.trx_number,

   rct.customer_trx_id,

   rctl.customer_trx_line_id,

   rctl.quantity_invoiced,

   unit_selling_price 

from

   ra_customer_trx_all rct,

   ra_customer_trx_lines_all rctl 

where

   rct.customer_trx_id = rctl.customer_trx_id 

   and trx_number = p_trx_number 

   and ct_reference = p_ct_refernce 

   and line_type = 'line';

begin

   for i_late_fees in c_fetch_late_fees loop 

   begin

      if i_late_fees.product_type = 'xx' 

   then

      mo_global.set_policy_context ('s', xx);

l_org_id:= xx;

elsif i_late_fees.product_type in 

(

   'xx', 'xx'

)

then

   mo_global.set_policy_context ('s', xx);

l_org_id:= xx;

   end

   if;

exception 

when

   others 

then

   dbms_output.put_line('error setting org' || sqlerrm || sqlcode);

end

;

select

   to_date(i_late_fees.inv_date) - 5 into v_trx_date 

from

   dual;

for l_related_invoice in c_fetch_related_invoice (l_org_id , v_trx_date , i_late_fees.ct_reference ) loop l_payment_flag:= false;

open check_invoice_paid_yn (p_trx_number => l_related_invoice.trx_number, p_ct_refernce => l_related_invoice.ct_reference );

fetch check_invoice_paid_yn into lc;

if check_invoice_paid_yn % found 

then

   l_payment_flag:= true;

elsif check_invoice_paid_yn % notfound 

then

   l_payment_flag:= false;

end

if;

close check_invoice_paid_yn;

if l_payment_flag = true 

then

   for lc_inv in c_inv (l_trx_number, i_late_fees.ct_reference) loop dbms_output.put_line ('invoking credit memo creation process for trx_no and  ct_refernce are ' || l_trx_number || '  and   ' || i_late_fees.ct_reference);

ind := 1;

l_customer_trx_id := lc_inv.customer_trx_id;

l_cm_lines_tbl (ind).customer_trx_line_id := lc_inv.customer_trx_line_id;

l_cm_lines_tbl (ind).quantity_credited := lc_inv.quantity_invoiced * - 1;

l_cm_lines_tbl (ind).price := lc_inv.unit_selling_price;

l_cm_lines_tbl (ind).extended_amount := lc_inv.quantity_invoiced * lc_inv.unit_selling_price * - 1;

end

loop;

ar_credit_memo_api_pub.create_request ( -- standard api parameters

p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_false-- credit memo request parameters

, p_customer_trx_id => l_customer_trx_id, p_line_credit_flag => 'y', p_cm_line_tbl => l_cm_lines_tbl, p_cm_reason_code => 'return', p_skip_workflow_flag => 'y', p_batch_source_name => 'xx', p_interface_attribute_rec => v_interface_header_rec, p_credit_method_installments => null, p_credit_method_rules => null, x_return_status => v_return_status, x_msg_count => v_msg_count, x_msg_data => v_msg_data, x_request_id => v_request_id );

* / commit;

dbms_output.put_line ('message count ' || v_msg_count);

if v_msg_count = 1 

then

   dbms_output.put_line ('l_msg_data ' || v_msg_data);

elsif v_msg_count > 1 

then

   loop p_count := p_count + 1;

v_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

if v_msg_data is null 

then

   exit;

end

if;

dbms_output.put_line ('message' || p_count || ' ---' || v_msg_data);

end

loop;

end

if;

if v_return_status <> 's' 

then

   dbms_output.put_line ('failed');

else

   select

      cm_customer_trx_id into cm_trx_id 

   from

      ra_cm_requests_all 

   where

      request_id = v_request_id;

dbms_output.put_line (' cm trx_id = ' || cm_trx_id);

-- you can issue a commit; at this point if you want to save the created credit memo to the database

-- commit;

end

if;

end

if;

end

loop;

end

loop;

end

;

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;