Various Queries on Customer Data (HZ Tables)

Various Queries on Customer Data (HZ Tables)
 VARIOUS QUERIES ON CUSTOMER DATA (HZ TABLES)

Product: TCA / Oracle Receivables
Overview
There have been many instances where I have been asked by the Business Users to provide Customer Listing reports based on different parameters. The different requests typically are as follows
1. Customer listing with all Sites for a specific Org
2. A listing of only those Customer with whom we have had transactions in the last ‘x’ years
3. A listing of all Customer Sites that do not have any Business purpose associated with it
4. Customer Listing By Collector
5. Customer Listing along with Profile Class names and Collector names
6. And so on …
We have the Standard Customer Listing Reports in the application. However these reports are not org stripped because the HZ_CUST_ACCOUNTS data is not org stripped plus the standard reports could not be used for all different flavors of the Business requests. In these cases, I used the following queries
Queries
Customer listing with all Sites for a specific Org
execute dbms_application_info.set_client_info('Org_id') ;
SELECT
substrb(party.party_name,1,50) Customer_Name ,
cust.account_number Customer_Number ,
substrb(look.meaning, 1, 8) Site_Use ,
acct_site.cust_acct_site_id Address_Id,
substrb(loc.address1,1,30) Address_Line_1,
substrb(loc.city,1,15) City ,
substrb(loc.state,1,2) State ,
substrb(loc.postal_code,1,10) Zip_Code ,
cust.status Cust_Status
FROM
ar_lookups look,
ar_lookups look_status,
hz_cust_accounts cust,
hz_parties party,
hz_cust_site_uses site_uses,
hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc
WHERE
cust.cust_account_id = acct_site.cust_account_id
AND cust.party_id = party.party_id
AND acct_site.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
AND look.lookup_type(+) = 'SITE_USE_CODE'
AND look.lookup_code(+) = site_uses.site_use_code
AND look_status.lookup_type(+) = 'CODE_STATUS'
AND look_status.lookup_code(+) = nvl(cust.status, 'A')
ORDER BY
party.party_name ;
Customer listing with only Identifying Addresses for a specific Org
The IDENTIFYING_ADDRESS_FLAG column of HZ_PARTY_SITES table indicates if the Address is Identifying Address or not. (Values = Y or N).
(The Select and the From clause is the same as above query)
execute dbms_application_info.set_client_info('Org_id') ;
SELECT
. . .
FROM
. . .
WHERE
cust.cust_account_id = acct_site.cust_account_id
AND cust.party_id = party.party_id
AND acct_site.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
AND look.lookup_type(+) = 'SITE_USE_CODE'
AND look.lookup_code(+) = site_uses.site_use_code
AND look_status.lookup_type(+) = 'CODE_STATUS'
AND look_status.lookup_code(+) = nvl(cust.status, 'A')
AND party_site.identifying_address_flag = ‘Y’
ORDER BY
party.party_name ;
Listing of all Customer Sites that do not have any Business Purpose
The SITE_USE_CODE of the HZ_CUST_SITE_USES_ALL table stores the ‘Business Purpose’ code of the site. If we need a listing of Customer sites that do not have any Business Purpose, we add the where clause of ‘site_uses.site_use_code is NULL ‘ to the query. This listing was used for data cleanup purpose.
(The Select and the From clause is the same as above query)

execute dbms_application_info.set_client_info('Org_id') ;
SELECT
. . .
FROM
. . .
WHERE
cust.cust_account_id = acct_site.cust_account_id
AND cust.party_id = party.party_id
AND acct_site.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
AND look.lookup_type(+) = 'SITE_USE_CODE'
AND look.lookup_code(+) = site_uses.site_use_code
AND look_status.lookup_type(+) = 'CODE_STATUS'
AND look_status.lookup_code(+) = nvl(cust.status, 'A')
AND site_uses.site_use_code is NULL
ORDER BY
party.party_name ;
Listing of all Customer with Primary Bill To Address
The address with a ‘Bill To’ business purpose has the SITE_USE_CODE column of the HZ_CUST_SITE_USES_ALL table as ‘BILL_TO’
(The Select and the From clause is the same as above query)
execute dbms_application_info.set_client_info('Org_id') ;
SELECT
. . .
FROM
. . .
WHERE
cust.cust_account_id = acct_site.cust_account_id
AND cust.party_id = party.party_id
AND acct_site.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
AND look.lookup_type(+) = 'SITE_USE_CODE'
AND look.lookup_code(+) = site_uses.site_use_code
AND look_status.lookup_type(+) = 'CODE_STATUS'
AND look_status.lookup_code(+) = nvl(cust.status, 'A')
AND site_uses.site_use_code = 'BILL_TO'
AND site_uses.primary_flag = 'Y'
AND site_uses.status = 'A'
ORDER BY
party.party_name ;

Listing of all Customer with Bill To Address with whom we had transaction in the last 1 year
The BILL_TO_SITE_USE_ID of the RA_CUSTOMER_TRX_ALL table stores the SITE_USE_ID of HZ_CUST_SITE_USES_ALL table.
(The Select and the From clause is the same as above query)
execute dbms_application_info.set_client_info('Org_id') ;
SELECT
. . .
FROM
. . .
WHERE
cust.cust_account_id = acct_site.cust_account_id
AND cust.party_id = party.party_id
AND acct_site.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
AND look.lookup_type(+) = 'SITE_USE_CODE'
AND look.lookup_code(+) = site_uses.site_use_code
AND look_status.lookup_type(+) = 'CODE_STATUS'
AND look_status.lookup_code(+) = nvl(cust.status, 'A')
AND SITE_USES.site_use_id in
(SELECT distinct trx.bill_to_site_use_id from ra_customer_trx trx where trx.creation_date > sysdate - 365)
ORDER BY
party.party_name ;

Listing of Customer’s with Profile Class Name, Collector Name, Bill To Address
execute dbms_application_info.set_client_info('Org_id') ;
SELECT
substrb(party.party_name,1,50) Customer_Name ,
cust.account_number Customer_Number ,
pc.name Profile_Class_Name ,
coll.name Collector_Name ,
substrb(look.meaning, 1, 8) Site_Use ,
acct_site.cust_acct_site_id Address_Id,
substrb(loc.address1,1,30) Address_Line_1,
substrb(loc.city,1,15) City ,
substrb(loc.state,1,2) State ,
substrb(loc.postal_code,1,10) Zip_Code ,
cust.status Cust_Status
FROM
ar_lookups look,
ar_lookups look_status,
hz_cust_accounts cust,
hz_parties party,
hz_cust_site_uses site_uses,
hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_customer_profiles prof,
hz_cust_profile_classes pc ,
ar_collectors coll
WHERE
cust.cust_account_id = acct_site.cust_account_id
AND cust.party_id = party.party_id
AND acct_site.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
AND look.lookup_type(+) = 'SITE_USE_CODE'
AND look.lookup_code(+) = site_uses.site_use_code
AND look_status.lookup_type(+) = 'CODE_STATUS'
AND look_status.lookup_code(+) = nvl(cust.status, 'A')
AND cust.cust_account_id = prof.cust_account_id (+)
AND prof.collector_id = coll.collector_id(+)
AND prof.profile_class_id = pc.profile_class_id
AND prof.site_use_id is NULL
AND SITE_USES.site_use_id in
(SELECT distinct trx.bill_to_site_use_id from ra_customer_trx trx where trx.creation_date > sysdate - 60)
ORDER BY
party.party_name ;
Summary
For me these queries were very handy whenever I had any Customer Listing request from the Users. A little tweak here and there to these queries would fetch me all the data I needed. I always referred to the TRM to look for additional columns of these tables if there was any need to use them. A handy SQL query for a Consultant helps!

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

Queries to select the source data
1. Query to select the response note from customer calls. These notes are at the account level and hence have no reference to the transaction
SELECT acc_customer_id "CUST_ACCOUNT_ID"
, ano_text note_text
, rcu_customer_number account_number
, 'RESPONSE' note_type
, last_updated_by
, last_update_date
, last_update_login
, created_by
, creation_date
FROM ar_customer_calls_v
WHERE trunc(creation_date) >= trunc(&p_date_from)
AND trunc(creation_date) <= trunc(&p_date_to) ;
2. Query to select the customer call topic notes. If the customer_trx_id is NULL, it means that the note is at an account level. Else the note is at a transaction level.
SELECT cct_customer_trx_id customer_trx_id
, cct_customer_id cust_account_id
, ano_text note_text
, 'CALL_NOTE' note_type
, last_updated_by
, last_update_date
, last_update_login
, created_by
, creation_date
FROM ar_customer_call_topics_v
WHERE trunc(creation_date) >= trunc(&p_date_from)
AND trunc(creation_date) <= trunc(&p_date_to) ;
Check if Source data is already migrated
1. Query for checking if the account level note is already migrated
SELECT DISTINCT COUNT (*)
INTO x_count
FROM jtf_notes_b jtfn
, jtf_notes_tl jtfn_tl
WHERE
jtfn.source_object_code = 'IEX_ACCOUNT'
AND jtfn.source_object_id = note_rec.cust_acct_id
http://oracle.anilrpatil.com Page 3
AND jtfn_tl.jtf_note_id = jtfn.jtf_note_id
AND jtfn_tl.source_lang = 'US'
AND UPPER(jtfn_tl.notes) = UPPER(note_rec.note_text)
AND jtfn.creation_date = note_rec.creation_date
AND jtfn.created_by = note_rec.created_by
AND jtfn.last_update_date = note_rec.last_update_date
AND jtfn.last_updated_by = note_rec.last_updated_by;
In the above query, note_rec is a record_type consisting the source data. If the x_count variable is not zero, it means that the note is already migrated.
2. Query for checking if the invoice level note is already migrated
SELECT DISTINCT COUNT (*)
INTO x_count
FROM jtf_notes_b jtfn
, jtf_notes_tl jtfn_tl
WHERE
jtfn.source_object_code = 'IEX_INVOICES'
AND jtfn.source_object_id = note_rec.payment_schedule_id
AND jtfn_tl.jtf_note_id = jtfn.jtf_note_id
AND jtfn_tl.source_lang = 'US'
AND UPPER(jtfn_tl.notes) = UPPER(note_rec.note_text)
AND jtfn.creation_date = note_rec.creation_date
AND jtfn.created_by = note_rec.created_by
AND jtfn.last_update_date = note_rec.last_update_date
AND jtfn.last_updated_by = note_rec.last_updated_by;
In the above query, note_rec is a record_type consisting the source data. If the x_count variable is not zero, it means that the note is already migrated.
JTF_NOTES_PUB API
1) Account Level Notes – The Account level notes are migrated using the following code
Declare
l_notes_detail CLOB;
l_note_type VARCHAR(30) := 'IEX_HIST';
l_note_status VARCHAR2 (1) := 'I';
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_note_id NUMBER ;
l_msg_index_out NUMBER;
BEGIN
jtf_notes_pub.create_note
(p_api_version => 1.0,
p_init_msg_list => 'T',
p_commit => 'T',
p_jtf_note_id => NULL,
p_validation_level => 100,
p_source_object_id => note_rec.cust_account_id,
http://oracle.anilrpatil.com Page 4
p_source_object_code => 'IEX_ACCOUNT',
p_notes => note_rec.note_text ,
p_notes_detail => l_notes_detail,
p_entered_by => note_rec.created_by,
p_entered_date => note_rec.creation_date,
p_last_update_date => note_rec.last_update_date,
p_last_updated_by => note_rec.last_updated_by,
p_creation_date => note_rec.creation_date,
p_created_by => note_rec.created_by,
p_last_update_login => fnd_global.login_id,
p_note_type => l_note_type,
p_note_status => l_note_status,
x_jtf_note_id => l_note_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
IF (l_return_status <> 'S')
THEN
fnd_file.put_line(fnd_file.LOG,'l_return_status <> S ');
IF (fnd_msg_pub.count_msg > 0)
THEN
FOR i IN 1 .. fnd_msg_pub.count_msg
LOOP
fnd_msg_pub.get
(p_msg_index => i,
p_encoded => 'F',
p_data => l_msg_data,
p_msg_index_out => l_msg_index_out
);
DBMS_OUTPUT.put_line ('Error:' || l_msg_data);
fnd_file.put_line(fnd_file.LOG,'ERROR :' || l_msg_data);
END LOOP;
END IF;
END IF;
COMMIT;
END ;
In the above query, note_rec is a record_type consisting the source date.
The value of l_note_type should be a valid lookup_code for lookup_type JTF_NOTE_TYPE
SELECT LOOKUP_CODE,MEANING,DESCRIPTION,
TAG,START_DATE_ACTIVE,END_DATE_ACTIVE,ENABLED_FLAG,
LOOKUP_TYPE
FROM FND_LOOKUP_VALUES_VL WHERE (nvl('', territory_code) = territory_code or territory_code is null)
AND lookup_type = 'JTF_NOTE_TYPE'
order by LOOKUP_CODE ;
2) Invoice Level Notes – For migrating the Invoice level notes, the code remains the same except the value passed to the parameters p_source_object_id and p_source_object_code . The values that needs to be passed to these parameters are
http://oracle.anilrpatil.com Page 5
p_source_object_id => note_rec.payment_schedule_id ,
p_source_object_code => 'IEX_INVOICES’
Summary
This document details one approach for migrating Call Notes from AR to Advanced Collections. These notes can then be viewed in the Notes Tab of the Collections Form.
References
Oracle Common Applications Components – API Reference Guide
Oracle Advanced Collections Implementation Guide
Oracle Advanced Collections User Guide

Oracle Pending Transaction Queries

Oracle Pending Transaction Queries
Unprocessed Material:
SELECT *
  FROM mtl_material_transactions_temp
 WHERE     organization_id = :p_org_id
       AND transaction_date < TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date
       AND NVL (transaction_status, 0) <> 2 -- 2 indicates a save-only status;

Uncosted Material/WSM:
SELECT  /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */     *
  FROM mtl_material_transactions MMT
 WHERE     organization_id = :p_org_id
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date
       AND costed_flag IS NOT NULL;

Pending WIP Costing:
SELECT *
  FROM wip_cost_txn_interface
 WHERE organization_id = :p_org_id
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr'); --period close date

Pending WSM Interface:
SELECT *
  FROM wsm_split_merge_txn_interface
 WHERE     organization_id = :p_org_id
       AND process_status <> wip_constants.completed
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr'); --period close date

Pending LCM Interface:
/* Support for Landed Cost Management: Pending landed cost adjustment transactions */
SELECT *
  FROM cst_lc_adj_interface
 WHERE organization_id = :p_org_id
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr'); --period close date

Pending Receiving:
SELECT *
  FROM rcv_transactions_interface
 WHERE     to_organization_id = :p_org_id
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date
       AND destination_type_code IN ('INVENTORY', 'SHOP FLOOR');

Pending Material:
SELECT *
  FROM mtl_transactions_interface
 WHERE     organization_id = 102
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date
       AND process_flag <> 9;

Pending Shop Floor Move:
SELECT *
  FROM wip_move_txn_interface
 WHERE organization_id = :p_org_id
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr');

Incomplete Work Orders : If the maintenance is enabled in mtl parameters.eam_enabled_flag = ‘Y’

SELECT *
  FROM wip_discrete_jobs WDJ, wip_entities WE
 WHERE WDJ.organization_id = :p_org_id
       AND WDJ.scheduled_completion_date <=TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date
       AND WDJ.status_type = 3                                     -- Released
       AND WDJ.wip_entity_id = WE.wip_entity_id
       AND WDJ.organization_id = WE.organization_id
       AND WE.entity_type = 6                       -- Maintenance Work Order;

==============================================================================================================
Unprocessed Material:
SELECT COUNT(*)
  FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>' AND
       TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')
  AND NVL(TRANSACTION_STATUS, 0) <> 2

--Uncosted Material Transactions

SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */
 COUNT(*)
  FROM MTL_MATERIAL_TRANSACTIONS MMT
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')
   AND COSTED_FLAG IS NOT NULL

--Pending WIP Costing Transactions

SELECT COUNT(*)
  FROM WIP_COST_TXN_INTERFACE
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')

--Uncosted WSM Transactions
   
SELECT COUNT(*)
  FROM WSM_SPLIT_MERGE_TRANSACTIONS
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')

--Uncosted WSM Interface

SELECT COUNT(*)
  FROM WSM_SPLIT_MERGE_TXN_INTERFACE
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')

--Pending Receiving Transactions

SELECT COUNT(*)
  FROM RCV_TRANSACTIONS_INTERFACE
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')
   AND DESTINATION_TYPE_CODE = 'INVENTORY'

--Pending Material Transactions

SELECT COUNT(*)
  FROM MTL_TRANSACTIONS_INTERFACE
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')
   AND PROCESS_FLAG <> 9


--Pending Shop Floor Move Transactions

SELECT COUNT(*)
  FROM WIP_MOVE_TXN_INTERFACE
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')

--Unprocessed Shipping Transactions


SELECT COUNT(*)
  FROM WSH_DELIVERY_DETAILS     WDD,
       WSH_DELIVERY_ASSIGNMENTS WDA,
       WSH_NEW_DELIVERIES       WND,
       WSH_DELIVERY_LEGS        WDL,
       WSH_TRIP_STOPS           WTS
 WHERE WDD.SOURCE_CODE = 'OE'
   AND WDD.RELEASED_STATUS = 'C'
   AND WDD.INV_INTERFACED_FLAG IN ('N', 'P')
   AND WDD.ORGANIZATION_ID = 86
   AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
   AND WND.DELIVERY_ID = WDA.DELIVERY_ID
   AND WND.STATUS_CODE IN ('CL', 'IT')
   AND WDL.DELIVERY_ID = WND.DELIVERY_ID
   AND WTS.PENDING_INTERFACE_FLAG IN ('Y', 'P')
   AND TRUNC(WTS.ACTUAL_DEPARTURE_DATE) BETWEEN
       TO_DATE('27-OCT-2012 00:00:00', 'DD-MON-YYYY HH24:MI:SS') AND
       TO_DATE('23-NOV-2012 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
   AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID

Oracle apps important tables

OE_ORDER_HEADERS_ALL                       OE_HEADERS_IFACE_ALL
OE_ORDER_LINES_ALL                         OE_LINES_IFACE_ALL
OE_ORDER_HOLDS_ALL                         OE_ACTIONS_IFACE_ALL
OE_ORDER_SOURCES
OE_TRANSACTION_TYPES_TL/ALL
OE_PAYMENT_TYPES_TL


WSH_DELIVERY_DETAILS
WSH_NEW_DELIVERIES
WSH_DELIVERY_ASSIGNMENTS

OE_ORDER_HOLDS

RA_SALESREPS
OE_TRANSACTION_TYPES_TL
AR_CUSTOMERS
RA_TERMS

QP_LIST_HEADERS
QP_LIST_LINES
QP_PRICING_ATTRIBUTES

OE_ORDER_HEADERS_ALL oe_order_pub.process_order
OE_ORDER_LINES_ALL
OE_SALES_CREDITS


Oracle Apps Receivables (AR) Tables
Transactions
RA_CUSTOMER_TRX_ALL Transaction Header table
RA_CUSTOMER_TRX_LINES_ALL Transaction Lines table along with Tax lines.
RA_CUST_TRX_LINE_GL_DIST_ALL Distribution for Transaction Lines
RA_CUST_TRX_LINE_SALESREPS_ALL Salesrep information for Transaction Lines


Transaction Interface Tables
RA_INTERFACE_LINES_ALL Transaction Lines interface
RA_INTERFACE_SALESCREDITS_ALL Transaction Sales credit information
RA_INTERFACE_DISTRIBUTIONS_ALL Transaction Distribution information
RA_INTERFACE_ERRORS_ALL Transaction errors table
AR_PAYMENTS_INTERFACE_ALL Interface table to import receipts
AR_INTERIM_CASH_RECEIPTS_ALL Lockbox transfers the receipts that pass validation to the interim tables
AR_INTERIM_CASH_RCPT_LINES_ALL Lockbox transfers the receipts that pass validation to the interim tables


Receipts tables
AR_CASH_RECEIPTS_ALL Cash Receipt Header tables
AR_RECEIVABLE_APPLICATIONS_ALL stores Receipt Application details
AR_PAYMENT_SCHEDULES_ALL This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable
AR_CASH_RECEIPT_HISTORY_ALL This table stores all of the activity that is contained for the life cycle of a receipt.
• Each row represents one step.
• The status field for that row tells you which step the receipt has reached.
• Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.


Customer Tables
HZ_PARTIES A party is an entity that can enter into a business relationship.
HZ_CUST_ACCOUNTS This table stores information about customer/financial relationships established between a Party and the deploying company.
HZ_PARTY_SITES This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
HZ_CUST_ACCT_SITES_ALL This table stores information about customer/financial account sites information.
HZ_CUST_SITE_USES_ALL This table stores information about the business purposes assigned to a customer account site
HZ_LOCATIONS A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.


Setup tables
RA_CUST_TRX_TYPES_ALL This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.
AR_RECEIPT_CLASSES This table stores the different receipt classes that you define.
AR_RECEIPT_METHODS This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications