Order Management Queries

SELECT RCTA.*
  FROM OE_ORDER_HEADERS_ALL OOHA,
       RA_CUSTOMER_TRX_ALL RCTA
 WHERE TO_CHAR(OOHA.ORDER_NUMBER) = RCTA.INTERFACE_HEADER_ATTRIBUTE1
   AND OOHA.ORDER_NUMBER = 66431
  
================================================

SELECT WDA.DELIVERY_ID
  FROM OE_ORDER_HEADERS_ALL OOHA,
       WSH_DELIVERY_DETAILS WDD,
       WSH_DELIVERY_ASSIGNMENTS WDA
 WHERE OOHA.HEADER_ID = WDD.SOURCE_HEADER_ID
   AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
   AND OOHA.ORDER_NUMBER = '66431'  
  
==================================================

SELECT OTTT.NAME ORDER_TYPE
  FROM OE_ORDER_HEADERS_ALL OOHA,
       OE_TRANSACTION_TYPES_TL OTTT
 WHERE OOHA.ORDER_TYPE_ID = OTTT.TRANSACTION_TYPE_ID
   AND OOHA.ORDER_NUMBER = '66431'
   AND OTTT.LANGUAGE = 'US'
  
================================================

SELECT MSIB.SEGMENT1 ITEM,
       OOLA.ORDERED_QUANTITY
  FROM OE_ORDER_HEADERS_ALL OOHA,
       OE_ORDER_LINES_ALL OOLA,
       MTL_SYSTEM_ITEMS_B MSIB
 WHERE OOHA.HEADER_ID = OOLA.HEADER_ID
   AND OOLA.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
   AND OOLA.SHIP_FROM_ORG_ID = MSIB.ORGANIZATION_ID
   AND OOHA.ORDER_NUMBER = 66431

AR TABLES AND LINKS

NALAM1

KOE MONTLY

KOE 30 MONTLY

KOE PROFILE

12138

KOE123

COLLECTOS
===========
select * from ar_collectors where name='nalam1'

STATEMENT CYCLES
====================

SELECT * FROM AR_STATEMENT_CYCLES WHERE NAME='KOE MONTLY'

PAYMENT TERMS
====================

SELECT * FROM RA_TERMS_TL WHERE NAME='KOE 30 MONTLY'

CUSTOMER PROFILES
===================

SELECT * FROM HZ_CUST_PROFILE_CLASSES WHERE NAME='KOE PROFILE'

CUSTOMERS
============
SELECT * FROM HZ_PARTIES WHERE PARTY_NAME='KIRLOSKAR PUNE'

SELECT * FROM HZ_PARTY_SITES WHERE PARTY_ID= 405839

SELECT * FROM HZ_LOCATIONS WHERE LOCATION_ID= 26584

SELECT * FROM HZ_CUST_ACCOUNTS_ALL WHERE PARTY_ID=405839

SELECT * FROM HZ_CUST_ACCT_SITES_ALL WHERE CUST_ACCOUNT_ID=120764

SELECT * FROM HZ_CUST_SITE_USES_ALL WHERE CUST_ACCT_SITE_ID=11403

SELECT * FROM HZ_CONTACT_POINTS WHERE OWNER_TABLE_ID=224741

SELECT * FROM HZ_CUSTOMER_PROFILES WHERE CUST_ACCOUNT_ID=120764


AR TRANSACTIONS
=================

SELECT * FROM RA_CUSTOMER_TRX_ALL WHERE TRX_NUMBER='12138'

SELECT * FROM RA_CUSTOMER_TRX_LINES_ALL WHERE CUSTOMER_TRX_ID=757231

SELECT * FROM RA_CUST_TRX_LINE_GL_DIST_ALL WHERE CUSTOMER_TRX_ID=757231

SELECT * FROM RA_CUST_TRX_LINE_SALESREPS_ALL WHERE CUSTOMER_TRX_ID=757231

SELECT * FROM AR_PAYMENT_SCHEDULES_ALL WHERE CUSTOMER_TRX_ID=757231

RECEIPT
========

SELECT * FROM AR_CASH_RECEIPTS_ALL WHERE RECEIPT_NUMBER='KOE123'

SELECT * FROM AR_CASH_RECEIPT_HISTORY_ALL WHERE CASH_RECEIPT_ID=110043

SELECT * FROM AR_RECEIVABLE_APPLICATIONS_ALL WHERE CASH_RECEIPT_ID=110043

SELECT HP.PARTY_NAME CUSTOMER_NAME
  FROM HZ_PARTIES HP,
       HZ_CUST_ACCOUNTS_ALL HCAA,
       RA_CUSTOMER_TRX_ALL RCTA
 WHERE HP.PARTY_ID = HCAA.PARTY_ID
   AND HCAA.CUST_ACCOUNT_ID = RCTA.BILL_TO_CUSTOMER_ID
   AND RCTA.TRX_NUMBER = '12415'
  
=========================================

SELECT HCAA.ACCOUNT_NUMBER
  FROM HZ_PARTIES HP,
       HZ_CUST_ACCOUNTS_ALL HCAA
 WHERE HP.PARTY_ID = HCAA.PARTY_ID
   AND HP.PARTY_NAME = 'ASIAN PAINTS'  
  
  
=====================================
  
SELECT ACRA.RECEIPT_NUMBER
  FROM RA_CUSTOMER_TRX_ALL RCTA,
       AR_CASH_RECEIPTS_ALL ACRA,
       AR_RECEIVABLE_APPLICATIONS_ALL ARAA
 WHERE RCTA.CUSTOMER_TRX_ID = ARAA.APPLIED_CUSTOMER_TRX_ID
   AND ARAA.CASH_RECEIPT_ID = ACRA.CASH_RECEIPT_ID
   AND RCTA.TRX_NUMBER = '12415'
  
================================================

SELECT RCTTA.NAME
  FROM RA_CUSTOMER_TRX_ALL RCTA,
       RA_CUST_TRX_TYPES_ALL RCTTA
 WHERE RCTA.CUST_TRX_TYPE_ID = RCTTA.CUST_TRX_TYPE_ID
   AND RCTA.ORG_ID = RCTTA.ORG_ID
   AND RCTA.TRX_NUMBER = '12415'  
  
==================================================

SELECT HP.PARTY_NAME,
       HCAA.ACCOUNT_NUMBER,
       HCSUA.LOCATION,
       HL.ADDRESS1,
       HL.ADDRESS2,
       HL.ADDRESS3,
       HL.ADDRESS4,
       HL.CITY,
       HL.STATE,
       HL.COUNTRY,
       HL.POSTAL_CODE
  FROM RA_CUSTOMER_TRX_ALL RCTA,
       HZ_CUST_ACCOUNTS_ALL HCAA,
       HZ_CUST_ACCT_SITES_ALL HCASA,
       HZ_CUST_SITE_USES_ALL HCSUA,
       HZ_PARTIES HP,
       HZ_PARTY_SITES HPS,
       HZ_LOCATIONS HL
 WHERE RCTA.BILL_TO_CUSTOMER_ID = HCAA.CUST_ACCOUNT_ID
   AND HCAA.PARTY_ID = HP.PARTY_ID
   AND HCAA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
   AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
   AND HCSUA.SITE_USE_ID = RCTA.BILL_TO_SITE_USE_ID
   AND HP.PARTY_ID = HPS.PARTY_ID
   AND HPS.LOCATION_ID = HL.LOCATION_ID
   AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
   AND HCSUA.SITE_USE_CODE = 'BILL_TO'
   AND RCTA.TRX_NUMBER = '12415'

AP TABLES AND LINKS

20 MAR 2013:
=================================

PAYMENT AND TERMS:


SELECT *FROM AP_TERMS WHERE NAME='ARA_20_DAYNET'

BANK ACCOPUNTS TABALES:

SELECT * FROM HZ_PARTIES
WHERE PARTY_NAME ='HALFDINNERBANK'

SELECT * FROM HZ_PARTIES WHERE PARTY_NAME='HALFDINNER BEGUMPET'

SELECT * FROM CE_BANK_ACCOUNTS WHERE BANK_ID='436619' AND BANK_BRANCH_ID='436621'-- BANK_ID=PARTY_ID AND PARTY_ID=BANK_BRANCH_NAME


SELECT * FROM  AP_SUPPLIERS WHERE VENDOR_NAME LIKE 'ARA%'


SUPPLIERS TABLES:


SELECT * FROM  AP_SUPPLIERS WHERE VENDOR_NAME ='ARAVIND Technologies'

SELECT * FROM AP_SUPPLIER_SITES_ALL WHERE VENDOR_ID=55264

SELECT * FROM AP_SUPPLIER_CONTACTS WHERE VENDOR_SITE_ID=7334

SELECT * FROM IBY_EXT_BANK_ACCOUNTS WHERE BANK_ID= 436619

7978--DOCUMENT NUMBER

AR1001--INVOICE NUMBER

INVOICE TABLES:

SELECT * FROM AP_INVOICES_ALL WHERE  INVOICE_NUM='AR1001'

SELECT * FROM AP_INVOICE_LINES_ALL WHERE INVOICE_ID=212547

SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID=212547

SELECT * FROM AP_PAYMENT_SCHEDULES_ALL WHERE  INVOICE_ID=212547

PAYMENT TABLES:

SELECT * FROM AP_CHECKS_ALL WHERE CHECK_NUMBER=7978---    PICK CHEK NUMBER 
                                                    SELECT ACA.CHECK_NUMBER
                                                           FROM AP_CHECKS_ALL ACA,
                                                                AP_INVOICES_ALL AIA,
                                                                AP_INVOICE_PAYMENTS_ALL AIPA
                                                           WHERE AIA.INVOICE_NUM='AR1001'  
                                                           AND  AIA.INVOICE_ID=AIPA.INVOICE_ID
                                                           AND  AIPA.CHECK_ID=ACA.CHECK_ID
      --------------------------------

SELECT * FROM AP_INVOICE_PAYMENTS_ALL WHERE CHECK_ID=78177

21-03-2013:
=======================

select PHA.SEGMENT1 PO_NUMBER
     from po_headers_all pha,
          PO_LINES_ALL PLA,
          PO_LINE_LOCATIONS_ALL PLLA,
          PO_DISTRIBUTIONS_ALL PDA,
          ap_invoices_all aia,
          AP_INVOICE_LINES_ALL AILA
     where aia.INVOICE_NUM='MT1004'
     AND AIA.INVOICE_ID=AILA.INVOICE_ID
     AND PDA.PO_DISTRIBUTION_ID=AILA.PO_DISTRIBUTION_ID
     AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
     AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
     AND PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
    
SELECT ACA.CHECK_NUMBER
      FROM AP_CHECKS_ALL ACA,
           AP_INVOICES_ALL AIA,
           AP_INVOICE_PAYMENTS_ALL AIPA
      WHERE AIA.INVOICE_NUM='MT1004'  
      AND  AIA.INVOICE_ID=AIPA.INVOICE_ID
      AND  AIPA.CHECK_ID=ACA.CHECK_ID
     
SELECT     ASA.VENDOR_NAME   
       FROM AP_SUPPLIERS ASA,
            AP_INVOICES_ALL AIA
       WHERE AIA.VENDOR_ID = ASA.VENDOR_ID
       AND AIA.INVOICE_NUM='MT1004'    
      
SELECT AT.NAME TERM_NAME
     FROM AP_INVOICES_ALL AIA,
          AP_TERMS AT
     WHERE AIA.INVOICE_NUM='MT1004'
     AND  AIA.TERMS_ID=AT.TERM_ID   

PO TABLES and LINKS

SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1='14822'

SELECT * FROM PO_REQUISITION_LINES_ALL WHERE REQUISITION_HEADER_ID=182129

SELECT * FROM PO_REQ_DISTRIBUTIONS_ALL WHERE REQUISITION_LINE_ID=209330

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='R12' AND TYPE_LOOKUP_CODE='RFQ'

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=111628

SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=174467

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='R12'------YPE_LOOKUP_CODE='QUATATIONS'

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=111629

SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=174468

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='6821'

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=111630

SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=174469

SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE LINE_LOCATION_ID=265780

SELECT * FROM RCV_SHIPMENT_HEADERS WHERE RECEIPT_NUM='24121'

SELECT * FROM RCV_SHIPMENT_LINES WHERE SHIPMENT_HEADER_ID=4834293

SELECT * FROM RCV_TRANSACTIONS WHERE SHIPMENT_LINE_ID=4824861

---------------AUTO CREATE----------FUNCTION----------------------

SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1='14823'

SELECT * FROM PO_REQUISITION_LINES_ALL WHERE REQUISITION_HEADER_ID=182130

SELECT * FROM PO_REQ_DISTRIBUTIONS_ALL WHERE REQUISITION_LINE_ID=209331


SELECT ASP.VENDOR_NAME
  FROM PO_HEADERS_aLL PHA,
       AP_SUPPLIERS ASP
 WHERE ASP.VENDOR_ID = PHA.VENDOR_ID
   AND PHA.SEGMENT1 = '6884'
  
  
================================


SELECT MSIB.SEGMENT1 ITEM
  FROM MTL_SYSTEM_ITEMS_B MSIB,
       PO_HEADERS_ALL PHA,
       PO_LINES_ALL PLA,
       PO_LINE_LOCATIONS_ALL PLLA
 WHERE MSIB.INVENTORY_ITEM_ID = PLA.ITEM_ID
   AND MSIB.ORGANIZATION_ID = PLLA.SHIP_TO_ORGANIZATION_ID
   AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
   AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
   AND PHA.SEGMENT1 = '6884'
  
  
===========================================


SELECT PRHA.SEGMENT1 REQ_NUMBER
  FROM PO_REQUISITION_HEADERS_ALL PRHA,
       PO_REQUISITION_LINES_ALL PRLA,
       PO_REQ_DISTRIBUTIONS_ALL PRDA,
       PO_HEADERS_ALL PHA   ,
       PO_LINES_ALL PLA,
       PO_LINE_LOCATIONS_ALL PLLA,
       PO_DISTRIBUTIONS_ALL PDA
 WHERE PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
   AND PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID
   AND PRDA.DISTRIBUTION_ID = PDA.REQ_DISTRIBUTION_ID
   AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
   AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
   AND PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
   AND PHA.SEGMENT1 = '6884'

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

SELECT RSH.RECEIPT_NUM
  FROM PO_HEADERS_ALL PHA   ,
       PO_LINES_ALL PLA,
       PO_LINE_LOCATIONS_ALL PLLA,
       PO_DISTRIBUTIONS_ALL PDA,
       RCV_SHIPMENT_HEADERS RSH,
       RCV_SHIPMENT_LINES RSL
 WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
   AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
   AND PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
   AND PHA.PO_HEADER_ID = RSL.PO_HEADER_ID
   AND PLA.PO_LINE_ID = RSL.PO_LINE_ID
   AND PLLA.LINE_LOCATION_ID = RSL.PO_LINE_LOCATION_ID
   AND PLLA.SHIP_TO_ORGANIZATION_ID = RSL.TO_ORGANIZATION_ID
   AND PDA.PO_DISTRIBUTION_ID = RSL.PO_DISTRIBUTION_ID
   AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
   AND PHA.SEGMENT1 = '6884'  

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

SELECT PAPF.FULL_NAME BUYER_NAME
  FROM PER_ALL_PEOPLE_F PAPF,
       PO_HEADERS_ALL PHA
 WHERE PAPF.PERSON_ID = PHA.AGENT_ID
   AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
   AND PHA.SEGMENT1 = '6884'    

Inventory Tables and LINKS

SELECT * FROM MTL_PARAMETERS WHERE ORGANIZATION_CODE='AR6'

select * from mtl_secondary_inventories where organization_id=8313 and secondary_inventory_name='FGI'

SELECT * FROM MTL_ITEM_LOCATIONS WHERE ORGANIZATION_ID='8313'

========================================================================================
INVENTORY :

select * from mtl_system_items_b where segment1='lenovo 15"led'

select * from mtl_system_items_tl where inventory_item_id=237063

select * from mtl_item_categories  where  inventory_item_id =237063

select * from mtl_item_revisions where inventory_item_id=237063

select * from mtl_customer_items where customer_item_number='lenovo 15"led india'

select * from mtl_customer_item_xrefs where customer_item_id=13237


13-MARCH-2013
=========================

SELECT SUM(MR.RESERVATION_QUANTITY) ITEM
      FROM MTL_RESERVATIONS MR,
           MTL_SYSTEM_ITEMS_B MSIB
     WHERE MSIB.ORGANIZATION_ID=MR.ORGANIZATION_ID
      AND  MSIB.SEGMENT1='DELL 26" LCD'
     
SELECT MOQ.TRANSACTION_QUANTITY ONHAND_QUANTITY
       FROM MTL_ONHAND_QUANTITIES MOQ,
            MTL_SYSTEM_ITEMS_B MSIB,
            MTL_PARAMETERS MP
       WHERE MOQ.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID
       AND  MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID
       AND  MOQ.ORGANIZATION_ID=MSIB.ORGANIZATION_ID
      AND  MSIB.SEGMENT1='DELL 26" LCD'
       AND  MOQ.ORGANIZATION_ID=MP.ORGANIZATION_ID
       AND  MP.ORGANIZATION_CODE='M1'
      
      
SELECT ORGANIZATION_CODE MASTER_ORGANIZATION_CODE
      FROM  MTL_PARAMETERS MP
      WHERE ORGANIZATION_ID=MASTER_ORGANIZATION_ID

SELECT ORGANIZATION_CODE CHILD_INV_ORG_CODE
      FROM  MTL_PARAMETERS MP
      WHERE ORGANIZATION_ID<>MASTER_ORGANIZATION_ID
     
SELECT MPC.ORGANIZATION_CODE,MPM.ORGANIZATION_CODE MASTER_ORGANIZATION_CODE
     FROM MTL_PARAMETERS MPC,
          MTL_PARAMETERS MPM
     WHERE MPC.MASTER_ORGANIZATION_ID=MPM.ORGANIZATION_ID  
    
SELECT MPC.ORGANIZATION_CODE,DECODE(MPC.ORGANIZATION_CODE,MPM.ORGANIZATION_CODE,'ITSELF',MPM.ORGANIZATION_ID) MASTER_ORGANIZATION_CODE
     FROM MTL_PARAMETERS MPC,
          MTL_PARAMETERS MPM
     WHERE MPC.MASTER_ORGANIZATION_ID=MPM.ORGANIZATION_ID
    
SELECT MSIB.SEGMENT1 ITEMS
      FROM MTL_SYSTEM_ITEMS_B MSIB,
           MTL_PARAMETERS MP
      WHERE MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID
      AND MP.ORGANIZATION_CODE='M1'      INTERSECT
SELECT MSIB.SEGMENT1 ITEMS
      FROM MTL_SYSTEM_ITEMS_B MSIB,
           MTL_PARAMETERS MP
      WHERE MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID
      AND MP.ORGANIZATION_CODE='M2'  
     
SELECT MSIB.SEGMENT1 ITEMS
      FROM MTL_SYSTEM_ITEMS_B MSIB,
           MTL_PARAMETERS MP
      WHERE MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID
      AND MP.ORGANIZATION_CODE='M2'  
     MINUS
SELECT MSIB.SEGMENT1 ITEMS
      FROM MTL_SYSTEM_ITEMS_B MSIB,
           MTL_PARAMETERS MP
      WHERE MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID
      AND MP.ORGANIZATION_CODE='M1'
     
SELECT SUM(MOQ.TRANSACTION_QUANTITY) ONHAND_QUANTITY
      FROM MTL_ONHAND_QUANTITIES MOQ,
           MTL_SYSTEM_ITEMS_B MSIB
      WHERE MSIB.INVENTORY_ITEM_ID=MOQ.INVENTORY_ITEM_ID
      AND MSIB.ORGANIZATION_ID=MOQ.ORGANIZATION_ID
      AND MSIB.SEGMENT1='DELL 26" LCD'


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

14-march-2013

SELECT msib.segment1 item,
       msib.description,
       msib.primary_uom_code uom,
       mp.organization_code,
       sum(moq.transaction_quantity) onahnd_qty,
       moq.subinventory_code
  FROM MTL_SYSTEM_ITEMS_B MSIB,
       MTL_PARAMETERS MP,
       MTL_ONHAND_QUANTITIES MOQ
 WHERE SEGMENT1 = 'DELL 28" LCDR'
   AND MSIB.ORGANIZATION_ID = MP.ORGANIZATION_ID
   AND MP.ORGANIZATION_CODE = :P_ORGANIZATION_CODE
   AND NVL(MSIB.MTL_TRANSACTIONS_ENABLED_FLAG,'N') = 'N'
   AND MOQ.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
   AND MOQ.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
GROUP BY  msib.segment1 ,
          msib.description,
          msib.primary_uom_code,
          moq.subinventory_code,
          mp.organization_code
ORDER BY 4,1,6