8 Comments

  1. surendra babu
    Dec 08, 2011 @ 23:22:05

    hi

    Reply

    • Swamy
      Apr 23, 2012 @ 18:11:14

      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
    Jan 12, 2012 @ 19:33:55

    Can you pls post the answers as well?

    Reply

  3. MADHU BABU
    Mar 21, 2012 @ 17:13:41

    Hi,

    Please Post the answers as early

    Reply

  4. Swamy
    Apr 23, 2012 @ 18:18:04

    Above query for Pickslip

    Reply

  5. h
    Jun 08, 2012 @ 23:47:03

    Hi,
    can you please help me to know the API for Trading partner creation

    Reply

  6. pavan
    Jul 04, 2012 @ 18:48:23

    what is the link b/w purchage order to sales order

    Reply

  7. Raffygomez
    Jul 05, 2012 @ 11:48:04

    Questions:
    1. Is it possible to prompt the AP Clerk that a particular employee has pending un-liquidated cash advances?
    2. AR Adjustments. Despite particularly stating the Line# to be adjusted, system pro-rated the adjustment amount, any set-up for this problem?
    3. in AR transation, Any effect of credit memo line item in the inventory?
    4. AR-receipt# can be duplicated. No control even used for many times?
    5. In Order Mgt, Quantity in stock status were not deducted even after clicking the Book button?
    6. Tolerance/control shipped quantity vs SO quantity, no message if over the SO quantity?

    Reply

Leave a Reply