Order Management FAQ

  5 Responses to “Order Management FAQ”

1. 

   surendra babu says:

   December 8, 2011 at 11:22 pm

   hi

   Reply
   • 

     Swamy says:

     April 23, 2012 at 6:11 pm

     SELECT f.party_name, e.account_number cc, f.party_id, f.address1, f.address2,
     f.address3, f.address4, f.postal_code, f.province, f.city,
     DECODE (f.country, ‘OM’, ‘SULTANATE OF OMAN’, f.country) AS contry,
     f.party_name AS DO,
     (f.person_first_name || ” || f.person_last_name
     ) AS party_contact_person,
     f.address3, c.NAME delivery_number, UPPER (g.NAME) NAME,
     UPPER ( h.address_line_1
     || ‘ ‘
     || h.address_line_2
     || ‘ ‘
     || h.town_or_city
     || ‘ ‘
     || h.country
     ) AS address,
     h.telephone_number_1
     || ‘ ‘
     || h.telephone_number_2
     || ‘ ‘
     || h.telephone_number_3 telephone,
     b.transactional_curr_code, c.delivery_id, c.NAME AS delivery_name,
     c.confirm_date delivered_date, c.organization_id,b.ATTRIBUTE8 as remarks,
     (SELECT NAME
     FROM hr_all_organization_units haou
     WHERE haou.organization_id = wdd.organization_id) inv_org_name,
     b.transactional_curr_code, c.NAME invoice_id, b.cust_po_number AS po,
     b.order_number, b.ordered_date, d.ordered_item,
     (SELECT DISTINCT i.PRIMARY_UNIT_OF_MEASURE
     FROM mtl_system_items_b i
     WHERE i.inventory_item_id = d.inventory_item_id) PRIMARY_UNIT_OF_MEASURE,
     (SELECT DISTINCT i.description
     FROM mtl_system_items_b i
     WHERE i.inventory_item_id = d.inventory_item_id) description,
     d.packing_instructions AS pack_size, wdd.requested_quantity shipped_quantity,
     b.cust_po_number,d.inventory_item_id,
     (Select distinct t.segment1
     From hz_cust_accounts hca,
     hz_cust_acct_sites_all hcs,
     HZ_CUST_SITE_USES_ALL hcu,
     ra_territories t
     Where hca.CUST_ACCOUNT_ID=hcs.CUST_ACCOUNT_ID
     and hcs.CUST_ACCT_SITE_ID=hcu.CUST_ACCT_SITE_ID
     and hcu.TERRITORY_ID=t.TERRITORY_ID
     and hcu.SITE_USE_CODE=’SHIP_TO’
     and hca.CUST_ACCOUNT_ID= e.cust_account_id) territory,
     ( wdd.requested_quantity * msib.attribute1) AS qty_units,
     (Select Title||’ ‘||First_name||’ ‘||middle_names||’ ‘||last_name
     from per_all_people_f
     Where sysdate between effective_start_date and effective_end_date
     and person_id in (Select employee_id from fnd_user Where user_id=b.created_by)) emp_name,
     e.cust_account_id,rsa.NAME sales_person,wdd.LOT_NUMBER
     FROM oe_order_headers_all b,
     oe_order_lines_all d,RA_SALESREPS_ALL RSA,
     hz_cust_accounts e,
     hz_parties f,
     wsh_new_deliveries c,
     wsh_delivery_details wdd,
     wsh_delivery_assignments wda,
     hr_all_organization_units g,
     hr_locations_all h,
     mtl_system_items_b msib
     WHERE b.header_id = d.header_id
     AND e.cust_account_id = b.sold_to_org_id
     AND e.party_id = f.party_id
     AND wdd.source_header_id = b.header_id
     AND d.LINE_ID=wdd.SOURCE_LINE_ID
     AND wdd.delivery_detail_id = wda.delivery_detail_id
     AND c.delivery_id = wda.delivery_id
     AND b.org_id = g.organization_id
     AND g.location_id = h.location_id
     AND d.flow_status_code = ‘AWAITING_SHIPPING’
     AND msib.inventory_item_id=d.inventory_item_id
     and msib.inventory_item_id=wdd.inventory_item_id
     AND msib.organization_id=d.SHIP_FROM_ORG_ID
     and b.SALESREP_ID=rsa.SALESREP_ID
     AND c.organization_id = NVL (:p_inv_stc_org_id, c.organization_id)
     AND to_date(b.ordered_date) BETWEEN NVL (:p_from_date, b.ordered_date) AND NVL (:p_to_date, b.ordered_date)
     –AND to_date(b.ordered_date) BETWEEN to_date(NVL (:p_from_date, b.ordered_date)) AND to_date(NVL (:p_to_date, b.ordered_date))
     –AND b.order_number = ’100000288′

     /*
     SELECT distinct rownum sl, (count(*) OVER (PARTITION BY i.segment1 ORDER BY f.party_id DESC)) SLNO,f.party_name, e.account_number CC, f.party_id, f.address1, f.address2,
     f.address3, f.address4, f.postal_code, f.province, f.city,
     DECODE (f.country, ‘OM’, ‘SULTANATE OF OMAN’, f.country) AS contry,
     f.party_name AS DO,
     (f.person_first_name || ” || f.person_last_name
     ) AS party_contact_person,
     f.address3,
     –UPPER (j.address1) AS loc1,
     –UPPER (j.address2) AS loc2,
     c.name delivery_number,
     UPPER (g.NAME) NAME,
     UPPER ( h.address_line_1
     || ‘ ‘
     || h.address_line_2
     || ‘ ‘
     || h.town_or_city
     || ‘ ‘
     || h.country
     ) AS address,
     h.telephone_number_1
     || ‘ ‘
     || h.telephone_number_2
     || ‘ ‘
     || h.telephone_number_3 telephone,
     b.transactional_curr_code, c.delivery_id, c.NAME AS delivery_name,
     c.confirm_date delivered_date, c.organization_id, g1.NAME AS inv_name,
     b.transactional_curr_code, rt.NAME AS payment_terms, c.NAME invoice_id,
     rcta.trx_number AS invoice_num, rcta.trx_date AS invoice_date,
     apsa.due_date AS due_date, a.ship_to_location_id,
     hcsua.LOCATION bill_to_site, b.cust_po_number AS po, b.order_number,
     b.ordered_date, rsa.salesrep_id, rsa.NAME AS sales_rep, a.org_id,
     a.inventory_item_id, i.segment1, i.segment2 AS pack_code,
     a.requested_quantity, d.line_id, d.shipping_quantity_uom AS unit,
     d.unit_selling_price, a.delivery_detail_id, a.shipped_quantity,
     a.source_header_id, a.lot_number, a.source_line_id,
     d.packing_instructions AS pack_size, d.CONTEXT, d.attribute1 AS uom,
     i.primary_unit_of_measure,
     (a.shipped_quantity * TO_NUMBER (i.attribute1)) AS qty_units,
     –((a.shipped_quantity * to_number(i.ATTRIBUTE1)) * d.UNIT_SELLING_PRICE) as Total_price
     (a.shipped_quantity * d.unit_selling_price) AS total_price,
     i.description, d.ordered_item,
     b.cust_po_number,
     (SELECT distinct T.segment1 from RA_TERRITORIES t
     ,RA_SALESREP_TERRITORIES rs
     where t.TERRITORY_ID=rs.TERRITORY_ID
     and rs.SALESREP_ID=rsa.SALESREP_ID) territory
     FROM wsh_delivery_details a,
     oe_order_headers_all b,
     wsh_new_deliveries c,
     oe_order_lines_all d,
     hz_cust_accounts e,
     hz_parties f,
     hz_party_sites hps,
     hz_cust_acct_sites_all hcasa,
     hz_cust_site_uses_all hcsua,
     hr_all_organization_units g,
     hr_all_organization_units g1,
     hr_locations_all h,
     mtl_system_items_b i,
     hz_locations j,
     hz_party_sites k,
     wsh_dlvy_deliverables_v l,
     ra_salesreps_all rsa,
     –PARTY_ACC_DETAILS pad,
     ra_customer_trx_all rcta,
     ra_terms_tl rt,
     ar_payment_schedules_all apsa
     –RA_TERRITORIES R
     WHERE a.organization_id = i.organization_id
     AND g1.organization_id = a.organization_id
     AND a.inventory_item_id = i.inventory_item_id
     AND rsa.salesrep_id = b.salesrep_id
     AND j.location_id = a.ship_to_location_id
     AND k.location_id = j.location_id
     AND k.party_id = f.party_id
     AND a.source_header_id = b.header_id
     AND a.source_line_id = l.source_line_id
     AND a.org_id = b.org_id
     AND c.delivery_id = l.delivery_id
     AND a.delivery_detail_id = l.delivery_detail_id
     AND d.flow_status_code = ‘CLOSED’ —C.status_code=’CL’
     AND a.source_header_id = l.source_header_id
     AND b.header_id = d.header_id
     AND a.organization_id = c.organization_id
     AND rt.term_id = b.payment_term_id
     AND b.org_id = d.org_id
     AND a.customer_id = e.cust_account_id
     AND e.party_id = f.party_id
     AND a.org_id = g.organization_id
     AND g.location_id = h.location_id
     AND a.source_line_id = d.line_id
     AND hps.party_id = f.party_id
     AND hcsua.site_use_code LIKE ‘SHIP_TO’
     AND hcasa.party_site_id = hps.party_site_id
     AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
     AND rcta.interface_header_attribute1(+) = TO_CHAR (b.order_number)
     AND apsa.customer_trx_id = rcta.customer_trx_id
     AND e.cust_account_id = rcta.bill_to_customer_id
     –AND R.TERRITORY_ID=hcsua.TERRITORY_ID
     AND c.organization_id=nvl(:P_INV_STC_ORG_ID,c.organization_id)
     AND b.ORDERED_DATE between nvl(:P_FROM_DATE,b.ORDERED_DATE) and nvl(:P_TO_DATE,b.ORDERED_DATE)*/

     Reply
2. 

   Harsha says:

   January 12, 2012 at 7:33 pm

   Can you pls post the answers as well?

   Reply
3. 

   MADHU BABU says:

   March 21, 2012 at 5:13 pm

   Hi,

   Please Post the answers as early

   Reply
4. 

   Swamy says:

   April 23, 2012 at 6:18 pm

   Above query for Pickslip

   Reply

 Leave a Reply Cancel reply

[Social Login] Please enable at least one social network!

Name *

E-mail *

URI

CAPTCHA Code *

Your Comment

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>