Query on the Purchase Requisition, Purchase Order, Receipt in Oracle Apps R12

SELECT prha.segment1 requisition_number,
       prha.type_lookup_code,
       prha.authorization_status,
       prla.item_description,
       prla.quantity,
       pha.type_lookup_code,
       pha.segment1 po_number,
       pha.document_creation_method,
       pla.item_id,pla.list_price_per_unit,rsh.receipt_num
FROM  po_requisition_headers_all prha,--REQUISITION HEADERS TABLE
      po_requisition_lines_all  prla,--REQUISITIONS LINES TABLE
      po_req_distributions_all prda,--REQUISITIONS DISTRIBUTION TABLE
      po_headers_all pha, --PURCHASE ORDER HEADER TABLE
      po_lines_all pla,--PURCHASE ORDER LINE TABLE
      po_distributions_all pda,--PURCHASE ORDER DISTRIBUTIONS TABLE
      rcv_shipment_headers rsh,--RECEIPT HEADER TABLE
      rcv_shipment_lines rsl--RECEIPT LINE TABLE
WHERE prha.requisition_header_id = prla.requisition_header_id
and  prla.requisition_line_id = prda.requisition_line_id
and  pha.po_header_id = pla.po_header_id
and pla.po_line_id = pda.po_line_id
and prda.distribution_id = pda.req_distribution_id
and rsh.shipment_header_id = rsl.shipment_header_id
and pha.po_header_id = rsl.po_header_id
--and   prha.segment1 = '336276';
and rsh.shipment_header_id = '23163104';

BACK ORDERS QUERY in Oracle Apps R12

SELECT *
  FROM oe_order_headers_all ooha,
       wsh_delivery_details wdd,
       oe_order_lines_all oola
 WHERE     ooha.header_id = oola.header_id
       AND ooha.header_id = wdd.source_header_id
       AND wdd.released_status = 'B'
       AND NOT EXISTS
                  (SELECT 1
                     FROM wsh_delivery_details wdd1
                    WHERE     wdd1.released_status != 'B'
                          AND wdd1.source_header_id = ooha.header_id);

Relation Between Requisition and Purchase Order in Oracle Apps R12

SELECT prha.segment1 "REQ NUM", pha.segment1 "PO NUM"
  FROM po_headers_all pha,
       po_distributions_all pda,
       po_req_distributions_all prda,
       po_requisition_lines_all prla,
       po_requisition_headers_all prha
 WHERE     pha.po_header_id = pda.po_header_id
       AND pda.req_distribution_id = prda.distribution_id
       AND prda.requisition_line_id = prla.requisition_line_id
       AND prla.requisition_header_id = prha.requisition_header_id
       order by prha.segment1 desc;

How to Find All Cancel Requisitions in Oracle Apps

SELECT  prha.*
 FROM  po_Requisition_headers_all prha, po_action_history pah
 WHERE     1 = 1
       AND pah.object_id = prha.requisition_header_id
       AND action_code = 'CANCEL'
       AND pah.object_type_code = 'REQUISITION'
       ORDER BY requisition_header_id desc;

Join Between Oracle Tables

Joins:
GL   AND   AP
GL_CODE_COMBINATIONS                         AP_INVOICES_ALL
code_combination_id                          =              acct_pay_code_combination_id
GL_CODE_COMBINATIONS                         AP_INVOICES_DISTRIBUTIONS_ALL
code_combination_id                           =             dist_code_combination_id
GL_SETS_OF_BOOKS                                      AP_INVOICES_ALL
set_of_books_id                                  =             set_of_books_id
GL   AND AR
GL_CODE_COMBINATIONS                        RA_CUST_TRX_LINE__GL_DIST_ALL
code_combination_id                           =             code_combination_id
GL   AND INV
GL_CODE_COMBINATIONS                        MTL_SYSTEM_ITEMS_B
code_combination_id                         =          cost_of_sales_account
GL   AND PO
GL_CODE_COMBINATIONS                        PO_DISTRIBUTIONS_ALL
code_combination_id                            =           code_combination_id
PO AND AP
PO_DISTRIBUTIONS_ALL                              AP_INVOICE_DISTRIBUTIONS_ALL
Po_distribution_id                                  =             po_distribution_id
PO_VENDORS                                                   AP_INVOICES_ALL
vendor_id                                              =              vendor_id
PO AND SHIPMENTS
PO_HEADERS_ALL                                           RCV_TRANSACTIONS
Po_header_id                                       =              po_header_id
PO_DISTRIBUTIONS_ALL                              RCV_TRANSACTIONS
Po_distribution_id                                 =             po_distribution_id
SHIPMENTS AND INVOICE
RCV_TRANSACTIONS                                   AP_INVOICE_DISTRIBUTIONS_ALL
RCV_TRANSACTION_ID                  =          RCV_TRANSACTION_ID
PO AND  INV
PO_REQUISITION_LINES_ALL                  MTL_SYSTEM_ITEMS_B
item_id                                     =           inventory_item_id
org_id                                                 =           organization_id
PO AND HRMS
PO_HEADERS_ALL                                        HR_EMPLOYEES
Agent_id                                             =             employee_id
PO AND REQUISITION
PO_DISTRIBUTIONS_ALL                            PO_REQ_DISTRIBUTIONS_ALL
req_distribution_id                               =             distribution_id
SHIPMENTS AND INV
RCV_TRANSACTIONS                                  MTL_SYSTEM_ITEMS_B
Organization_id                                   =             organization_id
INV AND HRMS
MTL_SYSTEM_ITEMS_B                                HR_EMPLOYEES
buyer_id                                              =             employee_id
OM  AND  AR
OE_ORDER_HEADERS_ALL                          RA_CUSTOMER_TRX_LINES_ALL
TO_CHAR( Order_number)              =               interface_line_attribute1
OE_ORDER_LINES_ALL                                RA_CUSTOMER_TRX_LINES_ALL
TO_CHAR(Line_id)                         =                 interface_line_attribute6
OE_ORDER_LINES_ALL                                 RA_CUSTOMER_TRX_LINES_ALL
reference_customer_trx_line_id          =                customer_trx_line_id
OM AND SHIPPING
OE_ORDER_HEADERS_ALL                             WSH_DELIVARY_DETAILS
HEADER_ID                                        =             SOURCE_HEADER_ID
OE_ORDER_HEADERS_ALL                            WSH_DELIVARY_DETAILS
LINE_ID                                             =              SOURCE_LINE_ID
AP AND AR (BANKS)
AR_CASH_RECEIPTS_ALL                               AP_BANK_ACCOUNTS
REMITTANCE_BANK_ACCOUNT_ID   =                ABA.BANK_ACCOUNT_ID
AP AND AR
HZ_PARTIES                                                   AP_INVOICES_ALL
PARTY_ID                                        =               PARTY_ID
OM AND CRM
OE_ORDER_LINES_ALL                              CSI_ITEM_INSTANCES(Install Base)
LINE_ID                                       =               LAST_OE_ORDER_LINE_ID
Po_Requisition_Headers_All
Column Names                   Table Name                                  Column Name
REQUISITION_HEADER_IDPO_REQUISITION_LINES_ALL    REQUISITION_HEADER_ID
TYPE_LOOKUP_CODE        PO_DOCUMENT_TYPES             DOCUMENT_SUBTYPE
PREPARER_ID                   PER_PEOPLE_F                           PERSON_ID
ORG_ID                             MTL_SYSTEM_ITEMS               ORGANIZATION_ID
ORG_ID                             MTL_ORGANIZATIONS            ORGANIZATION_ID
Po_Requisition_Lines_All
Column Names                   Table Name                              Column Name
REQUISITION_HEADER_ID   PO_REQUISITION_HEADERS_ALL  REQUISITION_HEADER_ID
REQUISITION_LINE_ID      PO_REQ_DISTRIBUTIONS_ALL          REQUISITION_LINE_ID
LINE_TYPE_ID               PO_LINE_TYPES                                    LINE_TYPE_ID
ITEM_ID                          MTL_SYSTEM_ITEMS                        INVENTORY_ITEM_ID
ORG_ID                           MTL_SYSTEM_ITEMS                        ORGANIZATION_ID
Po_Requisition_Distributions_All
Column Names                      Table Name                                Column Name
REQUISITION_LINE_ID      PO_REQUISITION_LINES_ALL     REQUISITION_LINE_ID
DISTRIBUTION_ID            PO_DISTRIBUTIONS_ALL            REQ_DISTRIBUTION_ID
SET_OF_BOOKS_ID          GL_SETS_OF_BOOKS                     SET_OF_BOOKS_ID
CODE_COMBINATION_ID  GL_CODE-COMBINATIONS            CODE_COMBINATION_ID
Po_Distributions_All
Column Names                   Table Name                                         Column Name
PO_LINE_ID                   PO_LINES                                          PO_LINE_ID
REQ_DISTRIBUTION_ID   PO_REQ_DISTRIBUTIONS_ALL       DISTRIBUTION_ID
PO_DISTRIBUTION_ID    AP_INVOICE_DISTRIBUTIONS_ALL PO_DISTRIBUTION_ID
Po_Headers_All
Column Names                   Table Name                              Column Name
PO_HEADER_ID                PO_LINES                                 PO_HEADER_ID
PO_HEADER_ID                RCV_SHIPMENT_LINES           PO_HEADER_ID
VENDOR_ID                      PO_VENDORS                          VENDOR_ID
AGENT_ID                       PER_PEOPLE                           PERSON_ID
TYPE_LOOK_UP_CODE     PO_DOCUMENT_TYPES            DOCUMENT_SUBTYPE
Po_Lines_All
Column Names                   Table Name                                    Column Name
PO_HEADER_ID               PO_HEADERS                                PO_HEADER_ID
PO_LINE_ID                     PO_DISTRIBUTIONS_ALL           PO_LINE_ID
ITEM_ID                          MTL_SYSTEM_ITEMS                   ITEM_ID
Rcv_Shipment_Lines
Column Names                   Table Name                                  Column Name
PO_HEADER_ID                PO_HEADERS                              PO_HEADER_ID
SHIPMENT_HEADER_ID   RCV_SHIPMENT_HEADERS  SHIPMENT_HEADER_ID
Ap_Invoices_All
Column Names       Table Name                                          Column Name
INVOICE_ID             AP_INVOICE_DISTRIBUTIONS_ALL  INVOICE_ID
Oe_Order_Headers_All
Column Names                   Table Name                      Column Name
HEADER_ID                        OE_ORDER_LINES                   HEADER_ID
SOURCE_HEADER_ID          WISH_DELIVERY_DETAILS     SOURCE_HEADER_ID
PRICE_LIST_ID                   QP_LIST_HEADERS_TL           LIST_HEADER_ID
ORG_ID                              MTL_ORGANIZATIONS           ORGANIZATION_ID
SALESREP_ID                      JTF_RS_SALESREPS               SALESREP_ID
ORDER_TYPE_ID                  OE_TRANSACTION_TYPES     TRANSACTION_TYPE_ID
ORDER_SOURCE_ID             OE_ORDER_SOURCES             ORDER_SOURCE_ID
ACCOUNTING_RULE_ID        RA_RULES                               RULE_ID
PAYMENT_TERM_ID              RA_TERMS                              TERM_ID
SOLD_TO_ORG_ID               HZ_CUST_ACCOUNTS             CUST_ACCOUNT_ID
SHIP_FROM_ORG_ID            MTL_PARAMETERS                 ORGANIZATION_ID
SHIP_TO_ORG_ID                HZ_CUST_SITE_USES_ALL    SITE_USE_ID
Oe_Order_Lines_All
Column Names       Table Name                             Column Name
LINE_TYPE_ID            OE_TRANSACTION_TYPES_TL     TRANSACTION_TYPE_ID
INVOICING_RULE_ID  RA_RULES                                     RULE_ID
Hz_Parties
Column Names       Table Name                         Column Name
PATY_ID                    HZ_CUST_ACCOUNTS        PATY_ID
CUST_ACCOUNT_ID   OE_ORDER_LINES             SOLD_TO_ORG_ID
Hz_Party_Sites_All
Column Names                   Table Name                Column Name
PATY_ID                              HZ_PARTIES               PATY_ID
LOCATION_ID                     HZ_LOCATIONS         LOCATION_ID
Wsh_delivery_details
Column Names                 Table Name                              Column Name
SOURCE_HEADER_ID         OE_ORDER_HEADERS                    SOURCE_HEADER_ID
DELIVERY_DETAIL_ID        WSH_DELIVERY_ASSIGNMENTS   DELIVERY_DETAIL_ID
DELIVERY_ID                    WSH_NEW_DELIVERIES                DELIVERY_ID
INVENTORY_ITEM_ID        MTL_SYSTEM_ITEMS                     INVENTORY_ITEM_ID
RA_CUSTOMER_TRX_ALL
Column Names           Table Name                                    Column Name
CUSTOMER_TRX_ID     AR_RECEIVABLE_APPLICATIONS_ALL APPLIED_CUSTOMER_TRX_ID
TERM_ID                     RA_TERMS                                           TERM_ID
CUSTOMER_TRX_ID    RA_CUST_TRX_LINE_GL_DIST        CUSTOMER_TRX_ID
AR_CASH_REC EIPTS_ALL
Column Names                   Table Name                                   Column Name
CASH_RECEIPT_ID               AR_RECEIVABLE_APPLICATIONS_ALL  CASH_RECEIPT_ID
SET_OF_BOOKS_ID             GL_SETS_OF_BOOKS                               SET_OF_BOOKS_ID