Show 7 Days SQL using Connect by level

select to_char((trunc(sysdate, 'DAY') + (level - 1)), 'Day') dy

from   dual

connect by level <= ((sysdate + 7) - sysdate)

or

select to_char((trunc(sysdate, 'DAY') + (level - 1)), 'Day') dy

from   dual

connect by level <= 7

How does one add a day/hour/minute/second to a date value?

DATE is the datatype that we are all familiar with when we think about representing date and time values. It has the ability to store the month, day, year, century, hours, minutes, and seconds. It is typically good for representing data for when something has happened or should happen in the future. The problem with the DATE datatype is its' granularity when trying to determine a time interval between two events when the events happen within a second of each other. This issue is solved with the TIMESTAMP datatype. 
In order to represent the date stored in a more readable format, the TO_CHAR function has traditionally been wrapped around the date: 

SQL> SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "hiredate"
  FROM employees; 
hiredate
-------------------
17.12.1980:00:00:00
20.02.1981:00:00:00 
The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:

SQL> select sysdate, sysdate+1/24, sysdate +1/1440,sysdate +1/86400 from dual;

SYSDATE                        SYSDATE+1/24              SYSDATE+1/1440              SYSDATE+1/86400
--------------------           --------------------            --------------------               --------------------
03-Jul-2002 08:32:12     03-Jul-2002 09:32:12      03-Jul-2002 08:33:12        03-Jul-2002 08:32:13

Wondering how 30 seconds can be added to a timestamp, here is a typical example.


SQL>select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;

NOW                             NOW_PLUS_30_SECS
--------------------           --------------------
03-JUL-2005 16:47:23     03-JUL-2005 16:47:53

Seems pretty simple right ? :) here is a couple more that I did;

Description	Date Expression
Now	SYSDATE
Tomorow/ next day	SYSDATE + 1
Seven days from now	SYSDATE + 7
One hour from now	SYSDATE + 1/24
Three hours from now	SYSDATE + 3/24
An half hour from now	SYSDATE + 1/48
10 minutes from now	SYSDATE + 10/1440
30 seconds from now	SYSDATE + 30/86400
Tomorrow at 12 midnight	TRUNC(SYSDATE + 1)
Tomorrow at 8 AM	TRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noon	NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of the month at 12 midnight	TRUNC(LAST_DAY(SYSDATE ) + 1)
The next Monday, Wednesday or Friday at 9 a.m	TRUNC(LEAST(NEXT_DAY(sysdate,''MONDAY' ' ),NEXT_DAY(sysdate,''WEDNESDAY''), NEXT_DAY(sysdate,''FRIDAY'' ))) + (9/24)

Hope it was helpful, enjoy. - 

References: 
http://bkintsiful.blogspot.com/2012/04/how-does-one-add-dayhourminutesecond-to.html

Concurrent Program Details including Template Details if exists

SELECT ROWNUM

,fat.application_name application_name

,fat.application_short_name appl_short_name

,fcpt.user_concurrent_program_name concurrent_program_name

,fcp.concurrent_program_name concurrent_program_short_name

,fcp.output_file_type output_type

, fe.executable_name executable_name

,flv.meaning execution_method_type

,fat1.application_name executable_application_name

,fat1.application_short_name executable_appl_short_name

,xddt.data_source_name data_definition_name

,xtb.template_code template_code

, xtb.template_type_code template_type

,xl.file_content_type file_content

, xl.file_name rtf_file_name

, (SELECT user_name

     FROM fnd_user

     WHERE user_id = xtb.created_by) creation_user

  FROM fnd_concurrent_programs_tl fcpt,

       fnd_concurrent_programs fcp,

       fnd_executables fe,

       fnd_application_vl fat,

       fnd_application_vl fat1,

       fnd_lookup_values flv,

       xdo_ds_definitions_tl xddt,

       xdo_templates_b xtb,

       xdo_lobs xl

 WHERE fcp.concurrent_program_id = fcpt.concurrent_program_id

   AND fat.application_id = fcpt.application_id

   AND fcp.executable_id = fe.executable_id

   AND fcp.executable_application_id = fe.application_id

   AND fat1.application_id = fe.application_id

   AND xddt.data_source_code(+) = fcp.concurrent_program_name

   AND flv.lookup_type(+) = ‘CP_EXECUTION_METHOD_CODE’

   AND flv.lookup_code(+) = fe.execution_method_code

   AND xtb.data_source_code(+) = xddt.data_source_code

   AND xl.lob_code(+) = xtb.template_code

   AND xl.lob_type(+) = ‘TEMPLATE_SOURCE’

   AND flv.enabled_flag = ‘Y’

   –AND fcp.output_file_type=’XML’

   AND fcp.enabled_flag=’Y’

   AND NVL (flv.end_date_active, SYSDATE) >= SYSDATE 

   AND fcpt.user_concurrent_program_name=:p_concurrent_program_name

R12 AR Tables

------------------------------------Vision Instance---------------------------------------------------------
-------------CUSTOMER--------------------------------------
select *
from ra_customers_interface_all;

select *
from ra_customer_profiles_interface;

select *
from hz_cust_accounts
where account_name='Business World';

select *
from hz_cust_acct_sites_all
where cust_account_id=3347
and org_id=204;

select *
from hz_cust_site_uses_all hcsua
where hcsua.site_use_id=1000;
where hcsua.cust_acct_site_id=3634;

select *
from ar_customers
where customer_name='World of Business';
-----------------TRANSACTION----------------------------------
select *
from RA_INTERFACE_LINES_ALL;
RA_INTERFACE_SALESCREDITS
RA_INTERFACE_DISTRIBUTIONS
RA_INTERFACE_ERRORS (details about the failed records)
/

select *
from RA_CUSTOMER_TRX_ALL
where printing_original_date is not null
and sold_to_customer_id=1000
and trx_number=10000069 ;

select *
from RA_CUSTOMER_TRX_LINES_ALL
where customer_trx_id=1600;

select *
from Ra_Cust_Trx_Line_Gl_Dist_All
where customer_trx_line_id in (1802);

select *
from RA_CUST_TRX_TYPES_ALL;

select *
from XLA_DISTRIBUTION_LINKS xdl
where xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
and xdl.source_distribution_id_num_1=1314;


------------------RECEIPT---------------------------------

select *
from ar_payment_schedules_all
where customer_trx_id=1600;

select *
from ar_cash_receipts_all;

select *
from AR_RECEIVABLE_APPLICATIONS_ALL
where applied_customer_trx_id=1600;
and payment_schedule_id-1061;

select *
from ar_adjustments_all;

SQL to Verify to Ledger Setups - R12

SELECT GLV.LEDGER_CATEGORY_CODE,
       GLV.NAME,
       GLV.SHORT_NAME,
       GLV.DESCRIPTION,
       GLV.CHART_OF_ACCOUNTS_ID,
       GLV.PERIOD_SET_NAME                ACCOUNTING_CALENDAR,
       GLV.CURRENCY_CODE,
       GLV.SLA_ACCOUNTING_METHOD_CODE,
       GLV.FIRST_LEDGER_PERIOD_NAME,
       GLV.FUTURE_ENTERABLE_PERIODS_LIMIT,
       GLV.SLA_DESCRIPTION_LANGUAGE,
       A.CONCATENATED_SEGMENTS            ENTERED_CURRENCY, --GLV.SLA_ENTERED_CUR_BAL_SUS_CCID,
       B.CONCATENATED_SEGMENTS            RETAINED_EARNING, --GLV.RET_EARN_CODE_COMBINATION_ID,
       C.CONCATENATED_SEGMENTS            ROUNDING_ACCOUNT, -- GLV.ROUNDING_CODE_COMBINATION_ID ,
       GLV.ALLOW_INTERCOMPANY_POST_FLAG,
       GLV.ENABLE_JE_APPROVAL_FLAG,
       GLV.PERIOD_END_RATE_TYPE,
       E.USER_CONVERSION_TYPE, -- GLV.PERIOD_AVERAGE_RATE_TYPE,
       D.CONCATENATED_SEGMENTS            TRANSLATION_ACCOUNT,
       GLV.CUM_TRANS_CODE_COMBINATION_ID
  FROM GL_LEDGERS                GLV,
       GL_CODE_COMBINATIONS_KFV  A,
       GL_CODE_COMBINATIONS_KFV  B,
       GL_CODE_COMBINATIONS_KFV  C,
       GL_CODE_COMBINATIONS_KFV  D,
       GL_DAILY_CONVERSION_TYPES E
 WHERE E.CONVERSION_TYPE = GLV.PERIOD_AVERAGE_RATE_TYPE
   AND GLV.SLA_ENTERED_CUR_BAL_SUS_CCID = A.CODE_COMBINATION_ID
   AND GLV.RET_EARN_CODE_COMBINATION_ID = B.CODE_COMBINATION_ID
   AND GLV.ROUNDING_CODE_COMBINATION_ID = C.CODE_COMBINATION_ID
   AND GLV.CUM_TRANS_CODE_COMBINATION_ID = D.CODE_COMBINATION_ID;

Standard Query To Get Account Description

-- Function and procedure implementations
  FUNCTION To_Get_Account_Desc  (I_LEDGER_ID IN NUMBER,
                                 I_SEGMENT3  IN VARCHAR2,
                                 I_SEGMENT4  IN VARCHAR2,
                                 I_SEGMENT5  IN VARCHAR2) RETURN VARCHAR2 IS
 
  v_description1     Fnd_Flex_Values_Vl.description%TYPE;
  v_description2     Fnd_Flex_Values_Vl.description%TYPE;
  v_description3     Fnd_Flex_Values_Vl.description%TYPE;
 
  BEGIN
 
 SELECT v.description 
 INTO  v_description1
FROM  Fnd_Flex_Values_Vl v
   , fnd_flex_value_sets S
   , FND_ID_FLEX_SEGMENTS FIFS
   , GL_LEDGERS  L
WHERE v.FLEX_VALUE               = I_SEGMENT3
AND v.FLEX_VALUE_SET_ID          = s.flex_value_set_id
AND FIFS.APPLICATION_ID          = 101
AND FIFS.FLEX_VALUE_SET_ID       = S.FLEX_VALUE_SET_ID
AND FIFS.APPLICATION_COLUMN_NAME = 'SEGMENT3'
AND FIFS.ID_FLEX_CODE            = 'GL#'
AND L.CHART_OF_ACCOUNTS_ID       = FIFS.ID_FLEX_NUM
AND L.LEDGER_ID                  = I_LEDGER_ID;
 
 
SELECT   v.description 
INTO     v_description2
FROM  Fnd_Flex_Values_Vl v
   , fnd_flex_value_sets S
   , FND_ID_FLEX_SEGMENTS FIFS
   , GL_LEDGERS  L
WHERE v.FLEX_VALUE               = I_SEGMENT4
AND v.FLEX_VALUE_SET_ID          = s.flex_value_set_id
AND FIFS.APPLICATION_ID          = 101
AND FIFS.FLEX_VALUE_SET_ID       = S.FLEX_VALUE_SET_ID
AND v.parent_flex_value_low      = I_SEGMENT3
AND FIFS.APPLICATION_COLUMN_NAME = 'SEGMENT4'
AND  FIFS.ID_FLEX_CODE           = 'GL#'
AND L.CHART_OF_ACCOUNTS_ID       = FIFS.ID_FLEX_NUM
AND L.LEDGER_ID                  = I_LEDGER_ID;
 
 
 SELECT v.description 
 INTO  v_description3
FROM  Fnd_Flex_Values_Vl v
   , fnd_flex_value_sets S
   , FND_ID_FLEX_SEGMENTS FIFS
   , GL_LEDGERS  L
WHERE v.FLEX_VALUE               = I_SEGMENT5
AND v.FLEX_VALUE_SET_ID          = s.flex_value_set_id
AND FIFS.APPLICATION_ID          = 101
AND FIFS.FLEX_VALUE_SET_ID       = S.FLEX_VALUE_SET_ID
AND FIFS.APPLICATION_COLUMN_NAME = 'SEGMENT5'
AND  FIFS.ID_FLEX_CODE           = 'GL#'
AND L.CHART_OF_ACCOUNTS_ID       = FIFS.ID_FLEX_NUM
AND L.LEDGER_ID                  = I_LEDGER_ID; 
 
 
  RETURN(v_description1||'-'||v_description2||'-'||v_description3);
  EXCEPTION
 WHEN others THEN
  RETURN(NULL);                             
  END  To_Get_Account_Desc;

Query To check Scheduled Concurrent Programs and Request Sets details

SELECT fcr.request_id,
       DECODE(fcpt.user_concurrent_program_name,
              'Report Set',
              'Report Set:' || fcr.description,
              fcpt.user_concurrent_program_name) CONC_PROG_NAME,
       argument_text PARAMETERS,
       NVL2(fcr.resubmit_interval,
            'PERIODICALLY',
            NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
       DECODE(NVL2(fcr.resubmit_interval,
                   'PERIODICALLY',
                   NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
              'PERIODICALLY',
              'EVERY ' || fcr.resubmit_interval || ' ' ||
              fcr.resubmit_interval_unit_code || ' FROM ' ||
              fcr.resubmit_interval_type_code || ' OF PREV RUN',
              'ONCE',
              'AT :' ||
              TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
              'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,
       fu.user_name USER_NAME,
       requested_start_date START_DATE
  FROM apps.fnd_concurrent_programs_tl fcpt,
       apps.fnd_concurrent_requests    fcr,
       apps.fnd_user                   fu,
       apps.fnd_conc_release_classes   fcrc
 WHERE fcpt.application_id = fcr.program_application_id
   AND fcpt.concurrent_program_id = fcr.concurrent_program_id
   AND fcr.requested_by = fu.user_id
   AND fcr.phase_code = 'P'
   AND fcr.requested_start_date > SYSDATE
   AND fcpt.LANGUAGE = 'US'
   AND fcrc.release_class_id(+) = fcr.release_class_id
   AND fcrc.application_id(+) = fcr.release_class_app_id
   and  DECODE(fcpt.user_concurrent_program_name,
              'Report Set',
              'Report Set:' || fcr.description,
              fcpt.user_concurrent_program_name) like '%XXXXXXXX%'



---------------IST Time ---------------------------


SELECT fcr.request_id,
       DECODE(fcpt.user_concurrent_program_name,
              'Report Set',
              'Report Set:' || fcr.description,
              fcpt.user_concurrent_program_name) CONC_PROG_NAME,
       argument_text PARAMETERS,
       NVL2(fcr.resubmit_interval,
            'PERIODICALLY',
            NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
       DECODE(NVL2(fcr.resubmit_interval,
                   'PERIODICALLY',
                   NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
              'PERIODICALLY',
              'EVERY ' || fcr.resubmit_interval || ' ' ||
              fcr.resubmit_interval_unit_code || ' FROM ' ||
              fcr.resubmit_interval_type_code || ' OF PREV RUN',
              'ONCE',
              'AT :' ||
              TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
              'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,
       fu.user_name USER_NAME,
       requested_start_date START_DATE
   ,requested_start_date+5/24+(30/(24*60)) ISO_Start_date --- +5.30
  FROM apps.fnd_concurrent_programs_tl fcpt,
       apps.fnd_concurrent_requests    fcr,
       apps.fnd_user                   fu,
       apps.fnd_conc_release_classes   fcrc
 WHERE fcpt.application_id = fcr.program_application_id
   AND fcpt.concurrent_program_id = fcr.concurrent_program_id
   AND fcr.requested_by = fu.user_id
   AND fcr.phase_code = 'P'
   AND fcr.requested_start_date > SYSDATE
   AND fcpt.LANGUAGE = 'US'
   AND fcrc.release_class_id(+) = fcr.release_class_id
   AND fcrc.application_id(+) = fcr.release_class_app_id
   and  DECODE(fcpt.user_concurrent_program_name,
              'Report Set',
              'Report Set:' || fcr.description,
              fcpt.user_concurrent_program_name) like '%XXXX%'

IMPORTANT APPS QUERIES

OM ADVANCE PRICING QUERY

SELECT QPSEG.SEGMENT_MAPPING_COLUMN, QPSOUR.USER_SOURCING_TYPE,

QPSOUR.USER_VALUE_STRING, QPCON.PRC_CONTEXT_CODE,

QPSOUR.ATTRIBUTE_SOURCING_LEVEL, QPSOUR.REQUEST_TYPE_CODE,

QPREQ.PTE_CODE, QPCON.PRC_CONTEXT_TYPE, QPSEG.SEGMENT_CODE,

QPCON.SEEDED_FLAG CONTEXT_SEEDED_FLAG,

QPSEG.SEEDED_FLAG ATTRIBUTE_SEEDED_FLAG

FROM QP_SEGMENTS_B QPSEG,

QP_ATTRIBUTE_SOURCING QPSOUR,

QP_PRC_CONTEXTS_B QPCON,

QP_PTE_REQUEST_TYPES_B QPREQ,

QP_PTE_SEGMENTS QPPSEG

WHERE QPSOUR.SEGMENT_ID = QPSEG.SEGMENT_ID

AND QPPSEG.USER_SOURCING_METHOD = ‘ATTRIBUTE MAPPING’

AND QPSOUR.REQUEST_TYPE_CODE = ‘ONT’

AND QPSEG.PRC_CONTEXT_ID = QPCON.PRC_CONTEXT_ID

AND QPREQ.REQUEST_TYPE_CODE = QPSOUR.REQUEST_TYPE_CODE

AND QPPSEG.PTE_CODE = QPREQ.PTE_CODE

AND QPPSEG.SEGMENT_ID = QPSOUR.SEGMENT_ID

AND QPPSEG.SOURCING_ENABLED = ‘Y’

AND QPCON.PRC_CONTEXT_TYPE IN

(‘PRICING_ATTRIBUTE’, ‘PRODUCT’, ‘QUALIFIER’)

OM SALES ORDER REPORT

select oha.header_id,bill_ca.cust_account_id,
oha.order_number,
oha.ordered_date,
oha.cust_po_number customer_po,
oha.org_id,
ott.name order_type,
ola.line_number,
msi.segment1 item_code,
msi.description item_desc,
ola.ordered_quantity,
ola.order_quantity_uom,
oha.transactional_curr_code trx_curr,
ola.unit_selling_price,
(ola.ordered_quantity*ola.unit_selling_price) line_amt,
ola.shipping_quantity,
ola.shipping_quantity_uom,
(nvl(ola.shipping_quantity,0)*ola.unit_selling_price) shipped_amt,
ola.schedule_ship_date,
--oha.flow_status_code order_status,
ol.meaning order_status,
bill_p.party_name,
bill_su.LOCATION invoice_to_location,
bill_loc.address1 invoice_to_address1,
bill_loc.address2 invoice_to_address2,
bill_loc.address3 invoice_to_address3,
bill_loc.address4 invoice_to_address4,
DECODE (bill_loc.city,
NULL, NULL,
bill_loc.city || ', '
)
|| DECODE (bill_loc.state,
NULL, bill_loc.province || ', ',
bill_loc.state || ', '
)
|| DECODE (bill_loc.postal_code,
NULL, NULL,
bill_loc.postal_code || ', '
)
|| DECODE (bill_loc.country, NULL, NULL, bill_loc.country) invoice_to_address5,
ship_loc.city ship_city,
ship_loc.country ship_country_code,
ft.territory_short_name ship_country,
(select name
from ra_salesreps_all
where salesrep_id = oha.salesrep_id
and org_id = oha.org_id) sales_person,
--wd.date_scheduled,
oha.conversion_type_code
from oe_order_headers_all oha,
oe_order_lines_all ola,
oe_transaction_types_tl ott,
mtl_system_items_b msi,
hz_cust_site_uses_all bill_su,
hz_party_sites bill_ps,
hz_locations bill_loc,
hz_cust_acct_sites_all bill_cas,
hz_cust_accounts bill_ca,
hz_parties bill_p,
hz_cust_site_uses_all ship_su,
hz_party_sites ship_ps,
hz_locations ship_loc,
hz_cust_acct_sites_all ship_cas,
--    wsh_deliverables_v wd
fnd_territories_tl ft,
oe_lookups ol
where oha.org_id = nvl(:p_org_id,oha.org_id)
and trunc(oha.ordered_date) between nvl(:p_order_date_from,trunc(oha.ordered_date))
and nvl(:p_order_date_to,trunc(oha.ordered_date))
and bill_ca.cust_account_id between nvl(:p_cust_acc_id_from,trunc(bill_ca.cust_account_id))
and nvl(:p_cust_acc_id_to,trunc(bill_ca.cust_account_id))
and trunc(NVL(ola.schedule_ship_date,SYSDATE)) between nvl(:p_delvry_date_from,trunc(NVL(ola.schedule_ship_date,SYSDATE)))
and nvl(:p_delvry_date_to,trunc(NVL(ola.schedule_ship_date,SYSDATE)))
--and oha.flow_status_code between nvl(:p_order_status_from,oha.flow_status_code)
--and nvl(:p_order_status_to,oha.flow_status_code)
and ol.meaning between nvl(:p_order_status_from,ol.meaning)
and nvl(:p_order_status_to,ol.meaning)
and msi.inventory_item_id between nvl(:p_prod_desc_from,msi.inventory_item_id)
and nvl(:p_prod_desc_to,msi.inventory_item_id)
/*and msi.description between nvl(:p_prod_desc_from,msi.description)
and nvl(:p_prod_desc_to,msi.description) */
and ship_loc.country between nvl(:p_ship_country_from,ship_loc.country)
and nvl(:p_ship_country_to,ship_loc.country)
and oha.salesrep_id between nvl(:p_salesrep_id_from,oha.salesrep_id)
and nvl(:p_salesrep_id_to,oha.salesrep_id)
and oha.header_id = ola.header_id
and oha.ORDER_TYPE_ID = ott.transaction_type_id
and ott.language = userenv('LANG')
and ola.inventory_item_id = msi.inventory_item_id
and ola.ship_from_org_id = msi.organization_id
and oha.invoice_to_org_id = bill_su.site_use_id(+)
and bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id(+)
and bill_cas.party_site_id = bill_ps.party_site_id(+)
and bill_loc.location_id(+) = bill_ps.location_id
and bill_cas.cust_account_id = bill_ca.cust_account_id
and bill_ca.party_id = bill_p.party_id
and oha.ship_to_org_id = ship_su.site_use_id(+)
and ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
and ship_cas.party_site_id = ship_ps.party_site_id(+)
and ship_loc.location_id(+) = ship_ps.location_id
--   and oha.header_id = wd.source_header_id(+)
--   and ola.line_id = wd.source_line_id(+)
and ship_loc.country = ft.territory_code
and ft.language = userenv('LANG')
and ol.lookup_type = 'FLOW_STATUS'
and ol.lookup_code = oha.flow_status_code
--and oha.order_number = 21019 

INV ORGIN REPORT

SELECT  msib.inventory_item_id
,msib.organization_id              "Warehouse_Code"
,msib.segment1                     "Item"
,msib.description                  "Item_Desc"
,mtln.lot_number
,msib.primary_uom_code             "UOM"
,msib.primary_unit_of_measure
,msib.inventory_item_status_code
,msib.item_type
,msib.default_so_source_type
,mmt.transaction_id
,mmt.subinventory_code
,mmt.transfer_subinventory
,mmt.attribute1                    "Ship_to_warehouse_id"
,(select od.organization_name from org_organization_definitions od
where od.organization_id=mmt.attribute1 ) ship_to_warehouse
,mmt.attribute2 shipment_reference
,ood.organization_code
,ood.organization_name                     Warehouse
,TO_CHAR(TO_DATE(mmt.attribute3 ,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY') shipment_date
,ABS(mmt.transaction_quantity) quantity
,mmt.transaction_uom
--,mmt.actual_cost
--,mmt.prior_cost
--,mmt.new_cost
,( hou.address_line_1 ||''||
address_line_2||''||
address_line_3||''||
region_1||''||
region_2||''||
region_3||''||
telephone_number_1||''||
telephone_number_2||''||
telephone_number_3||''||
postal_code||''||
town_or_city||''||
country)ship_to_adderss
--,mmt.locator_id
,mmt.currency_code
FROM    mtl_system_items_b              msib
,mtl_material_transactions       mmt
,mtl_transaction_lot_numbers     mtln
,hr_organization_units_v         hou
,org_organization_definitions    ood
WHERE   msib.inventory_item_id=mmt.inventory_item_id
AND     msib.organization_id=mmt.organization_id
AND     msib.organization_id=ood.organization_id
AND     mmt.attribute1=hou.organization_id(+)
--AND     mmt.transaction_id=185011--162022--178029
AND     ood.organization_code=:inventory_org--parametors
--AND     mmt.attribute3 BETWEEN :from_date AND :to_date--parametors
AND     TO_CHAR(TO_DATE(mmt.attribute3 ,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY')
BETWEEN NVL(:from_date,TO_CHAR(TO_DATE(mmt.attribute3 ,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY') )
AND NVL(:to_date,TO_CHAR(TO_DATE(mmt.attribute3 ,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY') )
AND     mmt.transaction_id=mtln.transaction_id
AND     mmt.subinventory_code like 'IWM'

AP CHECKS STATUS INFORMATION

SELECT    PVS.Attribute6 "SUPPLIER SITE TYPE"
,GSOB.NAME "SET OF BOOKS NAME"
,AC.CHECK_NUMBER
 ,AC.CHECK_DATE
 ,TRUNC(SYSDATE-AC.CHECK_DATE) "AGE OF CHECK"
 ,AI.PAYMENT_CURRENCY_CODE "PAYMENT CURRENCY CODE"
 ,AC.AMOUNT "CHECK AMOUNT"
 ,AIA.INVOICE_NUM "INVOICE NUMBER"
 ,AI.INVOICE_CURRENCY_CODE "INVOICE CURRENCY CODE"
 ,AI.AMOUNT_PAID "INVOICE AMOUNT PAID"
 ,PV.VENDOR_NAME "SUPPLIER NAME"
 ,PVS.VENDOR_SITE_CODE "SUPPLIER SITE CODE"
 ,AID.DESCRIPTION "DESCRIPTION"
 ,PVS.ADDRESS_LINE1 "ADDRESS LINE1"
 ,PVS.ADDRESS_LINE2 "ADDRESS LINE2"
 ,PVS.ADDRESS_LINE3 "ADDRESS LINE3"
 ,PVS.CITY "CITY"
 ,PVS.STATE "STATE"
 ,PVS.ZIP "ZIP"
FROM   PO_VENDOR_SITES PVS
,GL_SETS_OF_BOOKS GSOB
,AP_CHECKS AC
 ,AP_INVOICES AI
 ,AP_INVOICES_ALL AIA
 ,PO_VENDORS PV
 ,AP_INVOICE_DISTRIBUTIONS AID

SELECT TAX JOURNALS

SELECT

GLJH.JE_HEADER_ID "HID",

GLJH.TAX_STATUS_CODE "TC",

GLJL.TAX_CODE_ID "ID",

TO_CHAR(GLJH.LAST_UPDATE_DATE,'DD-MON-YYYY') "UPDATE_DATE"

FROM GL_JE_HEADERS GLJH,

GL_JE_LINES GLJL,

GL_CODE_COMBINATIONS GLCC,

GL_JE_BATCHES GLJB

WHERE

GLJH.JE_HEADER_ID=GLJL.JE_HEADER_ID AND

GLJL.CODE_COMBINATION_ID=GLCC.CODE_COMBINATION_ID AND

GLJH.JE_BATCH_ID=GLJB.JE_BATCH_ID AND

GLJH.TAX_STATUS_CODE='T'

order by GLJH.JE_HEADER_ID;

INVOICE PAYMENT CHECK

SELECT --PVSA.ADDRESS_LINE1 "SUPPLIER SITE TYPE"

GSOBA.NAME "SET OF BOOKS NAME"

,ACA.CHECK_NUMBER "CHECK NUMBER"

,ACA.CHECK_DATE "CHECK DATE"

,TRUNC(SYSDATE-ACA.CHECK_DATE) "AGE OF CHECK"

,AIA.PAYMENT_CURRENCY_CODE "PAYMENT CURRENCY CODE"

,ACA.AMOUNT "CHECK AMOUNT"

,AIA.INVOICE_NUM "INVOICE NUMBER"

,AIA.INVOICE_CURRENCY_CODE "INVOICE CURRENCY CODE"

,AIA.AMOUNT_PAID "INVOICE AMOUNT PAID"

,PV.VENDOR_NAME "SUPPLIER NAME"

,PVSA.VENDOR_SITE_CODE "SUPPLIER SITE CODE"

,AIDA.DESCRIPTION "DESCRIPTION"

,PVSA.ADDRESS_LINE1 "ADDRESS LINE1"

,PVSA.ADDRESS_LINE2 "ADDRESS LINE2"

,PVSA.ADDRESS_LINE3 "ADDRESS LINE3"

,PVSA.CITY "CITY"

,PVSA.STATE "STATE"

,PVSA.ZIP "ZIP"

FROM AP_INVOICES_ALL AIA

,AP_CHECKS_ALL ACA

,AP_INVOICE_PAYMENTS_ALL AIPA

,AP_INVOICE_DISTRIBUTIONS_ALL AIDA

,PO_DISTRIBUTIONS_ALL PDA

,PO_VENDOR_SITES_ALL PVSA

,PO_VENDORS PV

,GL_SETS_OF_BOOKS GSOBA

WHERE AIPA.CHECK_ID=ACA.CHECK_ID

AND AIA.INVOICE_ID=AIPA.INVOICE_ID

AND AIA.INVOICE_ID=AIDA.INVOICE_ID

AND PV.VENDOR_ID=PVSA.VENDOR_ID

AND AIA.VENDOR_ID=PV.VENDOR_ID

AND AIDA.PO_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID

AND AIA.SET_OF_BOOKS_ID=GSOBA.SET_OF_BOOKS_ID

Sales invoice

SELECT hou.NAME operating_unit_name,

rct.trx_number sales_invoice_number,

ooh.order_number,

rc.customer_name,

rsa.NAME sales_person_name,

msi.segment1 item_number,

mln.lot_number,

msi.primary_unit_of_measure primary_uom,

ool.ordered_quantity primary_quantity,

msi.secondary_uom_code secondary_uom,

ool.ordered_quantity2 secondary_quantity,

msi.organization_id inventory_org_id,

ool.org_id,

rctl.extended_amount amount

FROM oe_order_headers_all ooh,

oe_order_lines_all ool,

hr_operating_units hou,

ra_customer_trx_all rct,

ra_customer_trx_lines_all rctl,

ra_customers rc,

ra_salesreps_all rsa,

mtl_system_items_b msi,

mtl_lot_numbers mln

WHERE ooh.header_id = ool.header_id

AND hou.organization_id = ool.org_id

AND ool.reference_customer_trx_line_id = rctl.customer_trx_line_id

AND rct.sold_to_customer_id = rc.customer_id

AND ooh.salesrep_id = rsa.salesrep_id

AND mln.inventory_item_id = msi.inventory_item_id

AND mln.organization_id = msi.organization_id

Receipt

SELECT ACR.RECEIPT_NUMBER

,ARM.NAME RECEIPT_METHOD

,ACR.RECEIPT_DATE

,ACRH.GL_DATE

,ABB.BANK_NAME

,ABA.BANK_ACCOUNT_NUM

,HP.PARTY_NAME CUSTOMER_NAME

FROM AR_RECEIPT_METHODS ARM

,AR_CASH_RECEIPTS_ALL ACR

,AR_CASH_RECEIPT_HISTORY_ALL ACRH

,AP_BANK_ACCOUNTS ABA

,AP_BANK_BRANCHES ABB

,HZ_PARTIES HP

,HZ_CUST_ACCOUNTS HCA

WHERE ARM.RECEIPT_METHOD_ID=ACR.RECEIPT_METHOD_ID

AND ACR.CASH_RECEIPT_ID=ACRH.CASH_RECEIPT_ID

AND ACR.REMITTANCE_BANK_ACCOUNT_ID=ABA.BANK_ACCOUNT_ID(+)

AND ABA.BANK_ACCOUNT_ID=ABB.BANK_BRANCH_ID(+)

AND ACR.PAY_FROM_CUSTOMER=HCA.CUST_ACCOUNT_ID

AND HCA.PARTY_ID=HP.PARTY_ID

AR INVOICE

select rcta.trx_number invoice_num,

--sales_order_reference,

rct.gl_date,

rctl.inventory_item_id,

rct.amount,

rcta.trx_date invoice_date,

rctl.taxable_amount tax_amount

-- revenue_account_id,

--receivables_account_id,

from ra_customer_trx_lines_all rctl,

ra_cust_trx_line_gl_dist_all rct,

ra_customer_trx_all rcta

where rctcl.customer_trx_line_id=rct.customer_trx_line_id

and rctl.customer_trx_id=rcta.customer_trx_id

and rcta.customer_trx_id=rct.customer_trx_id

AP Internal Item Query

SELECT DISTINCT

TO_CHAR(api.invoice_date,'MM/DD/YYYY') Document_Date,

(SELECT segment7

FROM gl_code_combinations gc

WHERE gc.code_combination_id = aid.dist_code_combination_id

) Document_type,

(SELECT segment5

FROM gl_code_combinations gc

WHERE gc.code_combination_id = aid.dist_code_combination_id

) Company_Code, -- <<FR HBS - 2522 ;FR HPS HFS -2530; FR ECC-2092>>

NULL Posting_Date,

NULL Fiscal_period,

api.invoice_currency_code                Currency_Key,

DECODE(api.invoice_currency_code,

sob.currency_code,NULL,

SUBSTR(api.exchange_rate,1,

INSTR(api.exchange_rate,'.',1)-1)||

RPAD(SUBSTR(api.exchange_rate,

INSTR(api.exchange_rate,'.',1),

LENGTH(api.exchange_rate)),6,0)) Exchange_Rate,

api.invoice_num                          Document_Num,

NULL Header_Text,

DECODE(SIGN(api.invoice_amount),

1,31,-1,21,0,31,NULL) Posting_Key1,

pv.segment1                              Account_or_Matchcode1,

ABS(api.invoice_amount) Amount1,

apt.name                                     PAYMENT_TERMS,

( SELECT TO_CHAR(MAX(due_date),'MM/DD/YYYY')

FROM ap_payment_schedules_all

WHERE invoice_id = api.invoice_id) Baseline_date, -- As per option2 in Mapping doc.

SUBSTR(pv.segment1,1,18) Assignment_number1,

(SELECT segment2

FROM gl_code_combinations gc

WHERE gc.code_combination_id = aid.dist_code_combination_id

) Item_Text1,

DECODE(SIGN(api.invoice_amount),

1,40,-1,50,0,40,NULL) Posting_Key2,

NULL Account_or_Matchcode2,

ABS(NVL((SELECT SUM(apd.amount)

FROM ap_invoice_distributions_all apd

WHERE apd.invoice_id=api.invoice_id),NULL)) Amount2,

NULL Assignment_number2,

NULL Item_Text2, --Can concatenate PO#s and populate this if business needs it.

(SELECT segment4

FROM gl_code_combinations gc

WHERE gc.code_combination_id = aid.dist_code_combination_id

) Profit_Center, --To be mapped by Finance. For now, leave blank

DECODE

((SELECT COUNT(1)

FROM ap_payment_schedules_all

WHERE invoice_id = api.invoice_id

AND NVL(hold_flag,'N') = 'Y'),0,'N','Y'

) Payment_hold,

api.vendor_site_id                       Vendor_Site_ID

FROM

ap_invoices_all api,

ap_accounting_events_all apae,

ap_ae_headers_all apaeh,

gl_sets_of_books sob,

po_vendors pv,

gl_code_combinations gcc,

ap_invoice_distributions_all aid,

AP_TERMS_TL apt

WHERE apae.source_table='AP_INVOICES'

AND apae.event_status_code = 'ACCOUNTED'

AND apaeh.gl_transfer_flag = 'Y'

AND apaeh.gl_transfer_error_code IS NULL

AND NVL(api.payment_status_flag,'N')<>'Y'

AND api.cancelled_date IS NULL

AND api.cancelled_by IS NULL

AND api.cancelled_amount IS NULL

--AND api.invoice_id=apae.source_id

AND aid.accounting_event_id = apae.accounting_event_id

AND apae.accounting_event_id = apaeh.accounting_event_id

AND sob.set_of_books_id=api.set_of_books_id

AND api.accts_pay_code_combination_id = gcc.code_combination_id

AND api.vendor_id=pv.vendor_id

AND apt.term_id= api.terms_id

AND api.org_id = 403

AND gcc.segment1 = '547L'

AND gcc.segment2 LIKE '191%'

AND aid.invoice_id = api.invoice_id

AND aid.line_type_lookup_code NOT IN ('TAX')

AND ABS(api.invoice_amount) !='0'

AND api.invoice_id NOT IN (SELECT vouchno FROM ap_expense_report_headers_all)

Purchase Order Query

SELECT msi.segment1 Product

, msi.description Description

, hl.description Ship_to_location

-- Modified deliver_to_subinv to display Subinventory Name as part of CCD# 2010-04-1701

--, mse.description deliver_to_subinv

, mse.secondary_inventory_name deliver_to_subinv

, DECODE

SELECT DECODE(d.reservation_type, 1, 'On Demand', 2, 'Reserved') Res_type,
SUBSTR(u.user_name, 1, 20) User_name,
SUBSTR(i.segment1, 1, 10) Item,
c.customer_name cust_name,
c.customer_number cust_number,
h.order_number Ord_no,
substr(l.shipment_priority_code, 1, 10) ship_code,
h.date_ordered Date_ordered,
r.organization_code Org,
d.line_item_quantity demand_qty
FROM so_headers_all h,
mtl_demand d,
mtl_sales_orders s,
mtl_system_items i,
org_organization_definitions r,
fnd_user u,
ra_customers c,
so_lines_all l
WHERE h.created_by = u.user_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id
AND h.order_number = s.segment1
AND r.organization_id = d.organization_id
AND c.customer_id = h.customer_id
AND d.demand_source_line = l.line_id
AND l.header_id = h.header_id
AND d.line_item_quantity > 0
AND i.segment1 BETWEEN '&&item_from' AND '&&item_to'
AND d.demand_id IN
(SELECT MAX(d1.demand_id)
FROM mtl_demand d1
WHERE d1.line_item_quantity > 0
AND d1.demand_source_type in (2, 8)
AND d1.parent_demand_id IS NOT NULL
GROUP BY d1.reservation_type, d1.parent_demand_id)
ORDER BY
DECODE(d.reservation_type, 1, 'Demand', 2, 'Reserved'),
SUBSTR(u.user_name, 1, 20),
SUBSTR(i.segment1, 1, 10)

/

SELECT
l.transaction_reason_code reason_code,
c.customer_name cust_name,
SUBSTR(s.segment1, 1, 10) item,
h.order_number ord_no,
t.trx_number inv_no,
0 - NVL(l.ordered_quantity, 0) - NVL(l.cancelled_quantity, 0) rma_quantity,
NVL(tl.quantity_credited, 0) qty_credited,
NVL(tl.quantity_credited, 0) * NVL(unit_selling_price, 0) total_cr,
u.user_name creator_name,
h.creation_date creation_date
FROM fnd_user u,
mtl_system_items s,
ra_customers c,
ra_customer_trx_all t,
ra_customer_trx_lines_all tl,
so_headers_all h,
so_lines_all l
WHERE h.order_category  = 'RMA'
AND h.cancelled_flag is null
AND tl.customer_trx_id = t.customer_trx_id(+)
AND tl.interface_line_attribute6(+) = l.line_id
AND c.customer_id = h.customer_id
AND u.user_id = h.created_by
AND l.inventory_item_id = s.inventory_item_id
AND l.warehouse_id = s.organization_id
AND h.header_id = l.header_id
AND h.creation_date between to_date('&&from_date', 'DD-MON-RRRR')
AND to_date('&&to_date', 'DD-MON-RRRR')
ORDER BY 1, 2

/

SELECT

o.organization_code Org_name,
SUBSTR(msi.segment1, 1, 10) Item,
SUBSTR(msi.description, 1, 45) Item_desc,
sh.order_number Order_number,
sh.date_ordered Date_ordered,
SUBSTR(fu.user_name, 1, 12) Created_by,
NVL(sl.ordered_quantity, 0) - NVL(sl.cancelled_quantity, 0) Qty_ordered,
sl.selling_price*sl.ordered_quantity Ext_amount
FROM  so_headers_all sh,
mtl_system_items msi,
so_lines_all sl,
fnd_user fu,
org_organization_definitions o
WHERE sh.header_id  = sl.header_id
AND o.organization_id  = msi.organization_id
AND sh.order_category IN ('R', 'RMA')
AND  sl.inventory_item_id  = msi.inventory_item_id
AND sl.warehouse_id = msi.organization_id
AND  sh.created_by  = fu.user_id
AND  (sh.s1 = 5 or sh.s1 = 15)
AND sh.cancelled_flag is null
AND sh.creation_date BETWEEN TO_DATE('&&from_date, 'DD-MON-RR')
AND TO_DATE('&&to_date', 'DD-MON-RR')
ORDER BY
o.organization_code,
SUBSTR(msi.segment1, 1, 10),
SUBSTR(msi.description, 1, 45),
sh.order_number
/

CUSTOMER DETAILS

SELECT HS.PARTY_NUMBER,

HS.PARTY_NAME, HS.PARTY_TYPE,

HCA.ACCOUNT_NUMBER,

HCA.STATUS,

HCAA.PARTY_SITE_ID,

HCSU.SITE_USE_CODE,

HCSU.LOCATION

FROM HZ_PARTIES HS,

HZ_CUST_ACCOUNTS HCA,

HZ_CUST_ACCT_SITES_ALL HCAA,

HZ_CUST_SITE_USES_ALL HCSU

WHERE HS.PARTY_ID = HCA.PARTY_ID

AND HCA.CUST_ACCOUNT_ID = HCAA.CUST_ACCOUNT_ID

AND HCAA.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID

CUSTOMER – ORDER DETAILS – ITEM DETAILS

SELECT DISTINCT

RC.CUSTOMER_NAME,

RC.CUSTOMER_NUMBER, RC.CUSTOMER_ID,

OEH.ORDER_NUMBER, OEH.ORG_ID,

OEH.ORDER_SOURCE_ID,

OEH.ORIG_SYS_DOCUMENT_REF,

OEH.ORDERED_DATE,

OEH.PRICE_LIST_ID,

OEH.SOLD_FROM_ORG_ID,

OEH.SOLD_TO_ORG_ID,

OEH.INVOICE_TO_ORG_ID,

OEH.FLOW_STATUS_CODE,

OEH.BOOKED_DATE,

OEL.ORDERED_QUANTITY,

OEL.SHIPPED_QUANTITY,

OEL.SHIPPED_QUANTITY,

OEL.INVENTORY_ITEM_ID,

OEL.PRICE_LIST_ID, OEL.UNIT_SELLING_PRICE,

HS.PARTY_NAME, HS.PARTY_TYPE, MSI.SEGMENT1

--OTT.TRANSACTION_TYPE_CODE,OTT.NAME TRNAME

FROM OE_ORDER_HEADERS_ALL OEH,

OE_ORDER_LINES_ALL OEL, RA_CUSTOMERS RC,

HZ_PARTIES HS, MTL_SYSTEM_ITEMS_B MSI,

OE_TRANSACTION_TYPES OTT

WHERE OEH.HEADER_ID = OEL.HEADER_ID

AND OEH.SOLD_TO_ORG_ID = RC.CUSTOMER_ID

AND RC.PARTY_ID = HS.PARTY_ID

AND MSI.INVENTORY_ITEM_ID = OEL.INVENTORY_ITEM_ID

--AND OEH.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID

AND OEH.ORDER_NUMBER = '10396'

SELECT * FROM OE_PRICE_LISTS

SELECT * FROM QP_LIST_HEADERS_ALL

SELECT * FROM QP_LIST_LINES

SELECT * FROM OE_ORDER_LINES_ALL where price_list_id ='1000'

SELECT * FROM OE_PRICE_LISTS WHERE NAME = 'Corporate'

CUSTOMER – ORDERS – ITEMS - TERMS - PRICELISTS

SELECT DISTINCT RC.CUSTOMER_NAME,

RC.CUSTOMER_NUMBER, RC.CUSTOMER_ID,

OEH.ORDER_NUMBER, OEH.ORG_ID,

OEH.ORDER_SOURCE_ID,

OEH.ORIG_SYS_DOCUMENT_REF,

OEH.ORDERED_DATE,

OEH.PRICE_LIST_ID,

OEH.SOLD_FROM_ORG_ID,

OEH.SOLD_TO_ORG_ID,

OEH.INVOICE_TO_ORG_ID, OEH.FLOW_STATUS_CODE,

OEH.BOOKED_DATE, OEL.ORDERED_QUANTITY,

OEL.SHIPPED_QUANTITY,

OEL.SHIPPED_QUANTITY,

OEL.INVENTORY_ITEM_ID,

OEL.PRICE_LIST_ID,

OEL.UNIT_SELLING_PRICE,

HS.PARTY_NAME, HS.PARTY_TYPE,

MSI.SEGMENT1, OEPL.NAME LISTNAME,

RT.NAME TERMNAME, RT.DESCRIPTION

FROM OE_ORDER_HEADERS_ALL OEH,

OE_ORDER_LINES_ALL OEL, RA_CUSTOMERS

RC, HZ_PARTIES HS, MTL_SYSTEM_ITEMS_B MSI,

OE_PRICE_LISTS OEPL, RA_TERMS RT

WHERE OEH.HEADER_ID = OEL.HEADER_ID

AND OEH.SOLD_TO_ORG_ID = RC.CUSTOMER_ID

AND RC.PARTY_ID = HS.PARTY_ID

AND MSI.INVENTORY_ITEM_ID = OEL.INVENTORY_ITEM_ID

AND OEH.ORDER_NUMBER = '10396'

AND OEL.PRICE_LIST_ID = OEPL.PRICE_LIST_ID

AND OEH.PAYMENT_TERM_ID =RT.TERM_ID

--- find OUT the customer, line item, ordered qty  AND price info OF the ORDER :

SELECT h.order_number, org.NAME customer_name, h.ordered_date order_date,

ot.NAME order_type, s.NAME sales_rep, l.line_id, l.line_number,

l.inventory_item_id, si.segment1, l.ordered_quantity,

l.unit_selling_price,

NVL (l.ordered_quantity, 0) * NVL (l.unit_selling_price, 0) amount,

h.transactional_curr_code currency_code

FROM ra_salesreps s,

oe_transaction_types_tl ot,

oe_sold_to_orgs_v org,

mtl_system_items_vl si,

oe_order_lines_all l,

oe_order_headers_all h

WHERE h.order_number = 14463

AND h.org_id = 204

AND l.header_id = h.header_id

AND h.sold_to_org_id = org.organization_id

AND (h.cancelled_flag IS NULL OR h.cancelled_flag = 'N')

AND h.open_flag = 'Y'

AND l.open_flag = 'Y'

AND l.service_reference_line_id IS NULL

AND l.inventory_item_id = si.inventory_item_id

AND NVL (si.organization_id, 0) = 204 --Item master orgn

AND h.order_type_id = ot.transaction_type_id

AND h.salesrep_id = s.salesrep_id

AND h.org_id = s.org_id

ORDER BY l.line_id

/

--find customer, ship TO AND bill TO information OF an ORDER :

SELECT h.order_number, c.NAME customer_name, lk1.meaning freight_terms,

lk2.meaning fob, s.location_code ship_location_code,

s.address_line_1 ship_address1, s.address_line_2 ship_address2,

s.state ship_state, s.postal_code ship_zip, s.country ship_country,

b.location_code bill_location_code, b.address_line_1 bill_address1,

b.address_line_2 bill_address2, b.country bill_country

FROM ar_lookups lk2,

oe_lookups lk1,

oe_sold_to_orgs_v c,

oe_invoice_to_orgs_v b,

oe_ship_to_orgs_v s,

oe_order_headers_all h

WHERE h.order_number = '123'

AND h.org_id = '204'

AND h.ship_to_org_id = s.organization_id

AND h.invoice_to_org_id = b.organization_id

AND h.sold_to_org_id = c.organization_id

AND h.freight_terms_code = lk1.lookup_code(+)

AND lk1.lookup_type(+) = 'FREIGHT_TERMS'

AND lk2.lookup_code(+) = h.fob_point_code

AND lk2.lookup_type(+) = 'FOB'

/

-- find OUT ORDER AND line hold information :

SELECT ho.NAME hold_name, hs.hold_until_date, hs.hold_comment,

h.order_number, oh.header_id, oh.line_id, oh.order_hold_id,

l.item_identifier_type, l.inventory_item_id, l.ordered_item

FROM oe_order_holds_all oh,

oe_order_lines_all l,

oe_order_headers_all h,

oe_hold_definitions ho,

oe_hold_sources_all hs

WHERE h.order_number = '1234'

AND oh.header_id = h.header_id

AND (h.cancelled_flag IS NULL OR h.cancelled_flag = 'N')

AND h.open_flag = 'Y'

AND oh.hold_source_id = hs.hold_source_id

AND hs.hold_id = ho.hold_id

AND h.header_id = l.header_id(+)

AND l.open_flag = 'Y'

AND l.line_id = NVL (oh.line_id, l.line_id)

AND l.service_reference_line_id IS NULL

AND oh.hold_release_id IS NULL

AND NVL (h.org_id, 0) = '204'

AND NVL (l.org_id, 0) = NVL (h.org_id, 0)

ORDER BY ho.NAME, h.order_number

/

---find freight related info OF ORDER viz: freight carrier, ship method AND service LEVEL :

SELECT h.order_number, h.shipping_method_code, wc.carrier_name,

wcsm.service_level, wcsm.freight_code

FROM wsh_carrier_ship_methods_v wcsm,

wsh_carriers_v wc,

oe_order_headers_all h

WHERE h.order_number = 14463

AND h.org_id = 204

AND h.shipping_method_code = wcsm.ship_method_code(+)

AND NVL (wcsm.organization_id(+), 0) = 204 --Master Organization

AND wcsm.freight_code = wc.freight_code(+)

ORDER BY h.order_number

/

--find  price discounts AND surcharges ON ORDER lines :

SELECT h.order_number, l.line_number, pa.list_line_type_code,

pa.arithmetic_operator, pa.operand,

DECODE (pa.modifier_level_code,

'ORDER', l.unit_list_price

* l.ordered_quantity

* pa.operand

* SIGN (pa.adjusted_amount)

/ 100,

(pa.adjusted_amount * NVL (l.ordered_quantity, 0))

) discount_amt

FROM qp_list_headers_vl lh,

oe_price_adjustments pa,

oe_order_lines_all l,

oe_order_headers_all h

WHERE h.order_number = '12345'

AND h.header_id = l.header_id

AND h.org_id = l.org_id

AND h.header_id = pa.header_id

AND l.line_id = pa.line_id(+)

AND pa.list_header_id = lh.list_header_id

AND ( pa.list_line_type_code = 'DIS'

OR pa.list_line_type_code = 'SUR'

OR pa.list_line_type_code = 'PBH'

)

AND pa.applied_flag =

-- find Customer contacts AND their ROLES :

SELECT oc.job_title

, oc.party_site_id

, relationship_id

, relationship_type

, hp.party_name object_name

, object_id

, object_type

, object_table_name

, hr.party_id

, relationship_code

, subject_id

, subject_type

, subject_table_name

, oc.title

, oc.mail_stop

, oc.contact_key

, ocr.role_type

, directional_flag

FROM hz_org_contact_roles ocr

, hz_org_contacts oc

, hz_parties hp

, hz_relationships hr

WHERE hr.subject_id = '' --Party id of the customer

AND hr.object_id = hp.party_id

AND hr.relationship_id = oc.party_relationship_id

AND oc.org_contact_id = ocr.org_contact_id(+)

ORDER BY object_name

/

-- find communication channels FOR a customer :

SELECT contact_point_id, contact_point_type, email_address, phone_number, url,

contacts, status, owner_table_name, owner_table_id, primary_flag,

orig_system_reference

FROM hz_contact_points

WHERE owner_table_name = 'HZ_PARTIES'

AND owner_table_id = '123' --Party_id for 'Hilman and Associates'

/

-- find ORGANIZATION TYPE Party info :

/* Formatted on 2010/08/24 11:24 (Formatter Plus v4.8.0) */

SELECT hp.party_id, hp.party_number, hp.party_name, hca.account_number,

hca.cust_account_id, hop.organization_profile_id,

hop.effective_start_date, hop.effective_end_date,

hop.organization_name, hop.duns_number, hop.enquiry_duns,

hop.ceo_name, hop.ceo_title, hop.principal_name, hop.principal_title,

hop.legal_status, hop.control_yr, hop.employees_total,

hop.hq_branch_ind, hop.branch_flag, hop.oob_ind,

hop.line_of_business

FROM hz_organization_profiles hop, hz_cust_accounts hca, hz_parties hp

WHERE hp.party_name = 'xxxx'

AND hp.party_id = hca.party_id

AND hp.party_id = hop.party_id

AND SYSDATE BETWEEN hop.effective_start_date

AND NVL (hop.effective_end_date, SYSDATE + 1)

ORDER BY hca.account_number

/

--find supplier info :

/* Formatted on 2010/08/24 11:25 (Formatter Plus v4.8.0) */

SELECT pov.vendor_id, pov.vendor_name supplier, pov.vendor_type_lookup_code,

sl.location_code shipto_location, bl.location_code billto_location,

pov.customer_num, pov.ship_via_lookup_code, pov.fob_lookup_code,

rt.NAME terms, pov.set_of_books_id, pov.credit_status_lookup_code,

pov.credit_limit

FROM ra_terms rt, hr_locations bl, hr_locations sl, po_vendors pov

WHERE pov.vendor_name LIKE 'xxx%'

AND pov.ship_to_location_id = sl.location_id(+)

AND pov.bill_to_location_id = bl.location_id(+)

AND pov.terms_id = rt.term_id(+)

ORDER BY 1

/

--find supplier info :

SELECT pov.vendor_id, pov.vendor_name supplier, pov.vendor_type_lookup_code,

sl.location_code shipto_location, bl.location_code billto_location,

pov.customer_num, pov.ship_via_lookup_code, pov.fob_lookup_code,

rt.NAME terms, pov.set_of_books_id, pov.credit_status_lookup_code,

pov.credit_limit

FROM ra_terms rt, hr_locations bl, hr_locations sl, po_vendors pov

WHERE pov.vendor_name LIKE 'xxx%'

AND pov.ship_to_location_id = sl.location_id(+)

AND pov.bill_to_location_id = bl.location_id(+)

AND pov.terms_id = rt.term_id(+)

ORDER BY 1

/

-- find Supplier sites :

SELECT pov.vendor_name supplier, povs.vendor_site_id,

povs.vendor_site_code site, povs.address_line1 a1ddress,

povs.address_line2 a2ddress, povs.address_line3 a3ddress,

povs.city || ', ' || povs.state || ' ' || povs.zip a4ddress,

povs.ship_to_location_id, povs.bill_to_location_id,

povs.ship_via_lookup_code, povs.freight_terms_lookup_code,

povs.fob_lookup_code

FROM po_vendors pov, po_vendor_sites povs

WHERE pov.vendor_id = '' AND pov.vendor_id = povs.vendor_id

ORDER BY 1

/

-- TO find Supplier contacts :

SELECT vc.vendor_contact_id, vc.vendor_site_id, vc.first_name,

vc.middle_name, vc.last_name, vc.prefix, vc.title, vc.mail_stop,

vc.area_code, vc.phone, vc.department, vc.email_address, vc.url,

vc.alt_area_code, vc.alt_phone, vc.fax_area_code, vc.inactive_date,

vc.fax

FROM po_vendor_contacts vc

WHERE vc.vendor_site_id = ''

ORDER BY 1

/

SELECT ood.organization_code org_code

, a.master_organization_id master_org_id

, o1.organization_code master_org_cd

, o1.organization_name master_org_nm

, a.cost_organization_id cost_org_id

, o2.organization_code cost_org_cd

, o2.organization_name cost_org_nm

, a.source_organization_id source_org_id

, o3.organization_code source_org_cd

, o3.organization_name source_org_nm

, mfg1.meaning primary_cost_method

, mfg2.meaning negative_balance

, mfg11.meaning gl_update_code

, a.calendar_code calendar_code

, a.default_demand_class default_demand_class

, mfg12.meaning encumbrance_reversal_flag

, mfg3.meaning locator_control

, mfg4.meaning interorg_transfer_code

, DECODE (a.maintain_fifo_qty_stack_type,

NULL, '',

mfg6.meaning

) maintain_fifo_cost

, mfg7.meaning serial_number_type

, mfg8.meaning lot_number_uniqueness

, mfg9.meaning lot_number_generation

, DECODE (a.lot_number_zero_padding,

NULL, '',

mfg10.meaning

) lot_number_zero_padding

, b.rule_name atp_rule_name

, c.picking_rule_name picking_rule_name

, a.default_locator_order_value

, a.default_subinv_order_value

, a.interorg_trnsfr_charge_percent intorg_charge_percent

, a.auto_serial_alpha_prefix

, a.start_auto_serial_number

, a.auto_lot_alpha_prefix

, a.lot_number_length

, mfg13.meaning serial_generation

, mfg14.meaning source_type

, a.source_subinventory source_subinv

FROM mtl_parameters a,

mtl_atp_rules b,

mtl_picking_rules c,

org_organization_definitions ood,

org_organization_definitions o1,

org_organization_definitions o2,

org_organization_definitions o3,

mfg_lookups mfg1,

mfg_lookups mfg2,

mfg_lookups mfg3,

mfg_lookups mfg4,

mfg_lookups mfg6,

mfg_lookups mfg7,

mfg_lookups mfg8,

mfg_lookups mfg9,

mfg_lookups mfg10,

mfg_lookups mfg11,

mfg_lookups mfg12,

mfg_lookups mfg13,

mfg_lookups mfg14

WHERE a.organization_id = 207

AND a.master_organization_id = o1.organization_id(+)

AND a.cost_organization_id = o2.organization_id(+)

AND a.source_organization_id = o3.organization_id(+)

AND a.organization_id = ood.organization_id(+)

AND a.default_atp_rule_id = b.rule_id(+)

AND a.default_picking_rule_id = c.picking_rule_id(+)

AND mfg1.lookup_type(+) = 'MTL_PRIMARY_COST'

AND a.primary_cost_method = mfg1.lookup_code(+)

AND mfg2.lookup_type(+) = 'SYS_YES_NO'

AND a.negative_inv_receipt_code = mfg2.lookup_code(+)

AND mfg3.lookup_type(+) = 'MTL_LOCATION_CONTROL'

AND a.stock_locator_control_code = mfg3.lookup_code(+)

AND mfg4.lookup_type(+) = 'MTL_INTER_INV_TRANSFER'

AND a.matl_interorg_transfer_code = mfg4.lookup_code(+)

AND mfg6.lookup_type(+) = 'SYS_YES_NO'

AND a.maintain_fifo_qty_stack_type = mfg6.lookup_code(+)

AND mfg7.lookup_type(+) = 'MTL_SERIAL_NUMBER_TYPE'

AND a.serial_number_type = mfg7.lookup_code(+)

AND mfg8.lookup_type(+) = 'MTL_LOT_UNIQUENESS'

AND a.lot_number_uniqueness = mfg8.lookup_code(+)

AND mfg9.lookup_type(+) = 'MTL_LOT_GENERATION'

AND a.lot_number_generation = mfg9.lookup_code(+)

AND mfg10.lookup_type(+) = 'SYS_YES_NO'

AND a.lot_number_zero_padding = mfg10.lookup_code(+)

AND mfg11.lookup_type(+) = 'SYS_YES_NO'

AND a.general_ledger_update_code = mfg11.lookup_code(+)

AND mfg12.lookup_type(+) = 'SYS_YES_NO'

AND a.encumbrance_reversal_flag = mfg12.lookup_code(+)

AND mfg13.lookup_type(+) = 'MTL_SERIAL_GENERATION'

AND a.serial_number_generation = mfg13.lookup_code(+)

AND mfg14.lookup_type(+) = 'MTL_SOURCE_TYPES'

AND a.source_type = mfg14.lookup_code(+)

/

---Find items RESTRICTED TO a subinventory :

SELECT isb.inventory_item_id

, isb.organization_id

, si.segment1 item

, isb.secondary_inventory

, isb.primary_subinventory_flag

, isb.picking_order

, isb.min_minmax_quantity

, isb.max_minmax_quantity

, isb.inventory_planning_code

, isb.fixed_lot_multiple

, isb.minimum_order_quantity

, isb.maximum_order_quantity

, isb.source_type

, isb.source_organization_id

, isb.source_subinventory

FROM mtl_item_sub_inventories isb

, mtl_system_items si

WHERE isb.secondary_inventory = 'Stores'

AND isb.inventory_item_id = si.inventory_item_id

AND isb.organization_id = si.organization_id

ORDER BY 1

/

--find locators IN a subinventory:

SELECT

il.inventory_location_id,

il.organization_id,

il.subinventory_code,

il.description,

il.physical_location_id,

il.pick_uom_code,

il.dimension_uom_code,

il.LENGTH,

il.width,

il.height,

il.locator_status,

il.status_id,

l.meaning

FROM mfg_lookups l,

mtl_item_locations il

WHERE organization_id=207

AND subinventory_code='Stores'

AND il.inventory_location_type=l.lookup_code(+)

AND l.lookup_type (+) = 'MTL_LOCATOR_TYPES'

/

--find few business GROUPS SET up IN the INSTANCE :

SELECT business_group_id, NAME

FROM per_business_groups

WHERE LOWER (NAME) LIKE '%vision corporation%'

ORDER BY NAME

/

-- find legal entities associated WITH a SOB

SELECT organization_id legal_entity_id

, business_group_id

, NAME

, date_from

, date_to

, set_of_books_id

, vat_registration_number

FROM hr_legal_entities

WHERE set_of_books_id = '' AND business_group_id = ''

-- find inventory organizations FOR an operating unit :

/* Formatted on 2010/08/24 11:13 (Formatter Plus v4.8.0) */

SELECT organization_id

, organization_code

, organization_name,

(SELECT location_id

FROM hr_all_organization_units ou

WHERE od.organization_id = ou.organization_id) location_id

, user_definition_enable_date

, disable_date

, chart_of_accounts_id

, inventory_enabled_flag

, operating_unit

, legal_entity

, set_of_books_id

, business_group_id

FROM org_organization_definitions od

WHERE operating_unit = 204

ORDER BY organization_code

/

--- get item attributes NOT UNDER status control

SELECT meaning1 attrib_group, user_attribute_name_gui,

-- ,control_level, status_control_code,attribute_name,

-- attribute_group_id,data_type,

-- user_attribute_name,level_updateable_flag,

-- validation_code ,lookup_type1,

-- lookup_code1,enabled_flag1,lookup_type2,lookup_code2,

meaning2 control_level,

-- ,enabled_flag2,

-- lookup_type3,lookup_code3,

meaning3 status_control,

-- enabled_flag3,lookup_type4,lookup_code4,

meaning4 VALIDATION

-- ,enabled_flag4

FROM mtl_item_attributes_v

WHERE control_level IN (1, 2)

AND status_control_code IS NULL

AND user_attribute_name_gui IS NOT NULL

AND attribute_name IN (SELECT attribute_name

FROM mtl_item_attr_appl_inst_v)

ORDER BY attribute_group_id_gui, sequence_gui

/

-- get item status attribute controls

SELECT ia.attribute_group_id GROUP_ID, ia.user_attribute_name_gui,

lk.meaning controlled_at, ia.attribute_name,

--   ia.user_attribute_name,

ia.status_control_code,

ia.validation_code

FROM fnd_lookup_values lk, mtl_item_attributes ia

WHERE ia.control_level = lk.lookup_code

AND lk.lookup_type = 'ITEM_CONTROL_LEVEL_GUI'

ORDER BY ia.attribute_group_id, 1

/

-- find item status attributes :

SELECT mis.inventory_item_status_code item_status, mis.description,

mis.disable_date, av.attribute_name, av.attribute_value VALUE

FROM mtl_item_status mis, mtl_status_attribute_values av

WHERE mis.inventory_item_status_code = av.inventory_item_status_code

ORDER BY 1

/

-- get item attributes UNDER status control :

SELECT meaning1 attrib_group, user_attribute_name_gui,

-- ,control_level, status_control_code,attribute_name,

-- attribute_group_id,data_type,

-- user_attribute_name,level_updateable_flag,

-- validation_code ,lookup_type1, lookup_code1,enabled_flag1,lookup_type2,lookup_code2,

meaning2 control_level,

-- ,enabled_flag2,

-- lookup_type3,lookup_code3,

meaning3 status_control,

-- enabled_flag3,lookup_type4,lookup_code4,

meaning4 VALIDATION

-- ,enabled_flag4

FROM mtl_item_attributes_v

WHERE control_level IN (1, 2)

AND status_control_code IS NOT NULL

AND user_attribute_name_gui IS NOT NULL

AND attribute_name IN (SELECT attribute_name

FROM mtl_item_attr_appl_inst_v)

ORDER BY attribute_group_id_gui, sequence_gui

/

--- find an Item attribute info :

SELECT segment1 item, msi.description, inventory_item_id,

ml.meaning item_type,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.inventory_item_status_code

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.inventory_item_status_code')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.purchasing_item_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.purchasing_item_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.shippable_item_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.shippable_item_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.mtl_transactions_enabled_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.mtl_transactions_enabled_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.so_transactions_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.so_transactions_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.internal_order_enabled_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.internal_order_enabled_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.customer_order_enabled_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.customer_order_enabled_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.purchasing_enabled_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.purchasing_enabled_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.inventory_asset_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.inventory_asset_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.eng_item_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) = 'mtl_system_items.eng_item_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.inventory_item_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.inventory_item_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name

|| '.'

|| msi.service_item_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.service_item_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.internal_order_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.internal_order_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.build_in_wip_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.build_in_wip_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.bom_enabled_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.bom_enabled_flag')

ATTRIBUTE,

(SELECT ia.user_attribute_name_gui

|| '.'

|| msi.stock_enabled_flag

FROM mtl_item_attributes_v ia

WHERE LOWER (ia.attribute_name) =

'mtl_system_items.stock_enabled_flag')

ATTRIBUTE

FROM fnd_lookup_values ml, mtl_system_items msi

WHERE msi.segment1 LIKE 'AS18947%'

AND msi.organization_id = 204

AND msi.item_type = ml.lookup_code(+)

AND ml.lookup_type(+) = 'ITEM_TYPE'

ORDER BY 1, 2

/

--- find Item template attribute VALUES :

SELECT it.template_name, ita.attribute_name, ita.attribute_value

FROM mtl_item_templates it, mtl_item_templ_attributes ita

WHERE it.template_name LIKE 'xxx%'

AND it.template_id = ita.template_id

AND ita.attribute_value IS NOT NULL

ORDER BY 1, 2

/

--- find  item cross-REFERENCES :

/* Formatted on 2010/08/24 11:27 (Formatter Plus v4.8.0) */

SELECT msi.segment1 item, mcr.cross_reference_type reference_type,

mcr.cross_reference, mcr.description

FROM mtl_cross_references mcr, mtl_system_items msi

WHERE mcr.cross_reference_type = 'Vendor'

AND mcr.inventory_item_id = msi.inventory_item_id

AND mcr.organization_id = msi.organization_id

ORDER BY 1, 2

/

-- find Customer items :

/* Formatted on 2010/08/24 11:27 (Formatter Plus v4.8.0) */

SELECT hp.party_name customer, ci.customer_item_number,

ci.customer_item_desc, msi.segment1 item, msi.description item_desc,

ci.customer_category_code, ci.item_definition_level,

ci.commodity_code_id, ci.address_id

FROM hz_parties hp,

hz_cust_accounts hca,

mtl_system_items msi,

mtl_customer_items ci,

mtl_customer_item_xrefs ix

WHERE ci.customer_item_id = ix.customer_item_id

AND ix.inventory_item_id = msi.inventory_item_id

AND ix.master_organization_id = msi.organization_id

AND ci.customer_id = hca.cust_account_id

AND hca.party_id = hp.party_id

ORDER BY 1, 2

/

---find Manufacturer items :

SELECT mm.manufacturer_name, mp.mfg_part_num, mp.description,

msi.segment1 inv_item, msi.description item_desc

FROM mtl_system_items msi, mtl_mfg_part_numbers mp, mtl_manufacturers mm

WHERE mm.manufacturer_id = mp.manufacturer_id

AND mp.inventory_item_id = msi.inventory_item_id

AND mp.organization_id = msi.organization_id

ORDER BY 1, 2

/

--find related items :

SELECT ito.segment1 item, ito.description, itr.segment1 related_item,

itr.description, ml.meaning relation, ri.reciprocal_flag

FROM mfg_lookups ml,

mtl_system_items itr,

mtl_system_items ito,

mtl_related_items ri

WHERE ri.inventory_item_id = ito.inventory_item_id

AND ri.organization_id = ito.organization_id

AND ri.related_item_id = itr.inventory_item_id

AND ri.organization_id = itr.organization_id

AND ri.relationship_type_id = ml.lookup_code

AND ml.lookup_type(+) = 'MTL_RELATIONSHIP_TYPES'

ORDER BY 1, 2

/

-- find DEFAULT category FOR a category SET :

/* Formatted on 2010/08/24 11:28 (Formatter Plus v4.8.0) */

SELECT mcats.category_set_name, mcat.segment1 default_category,

mcat.description cat_desc, mcat.category_id, mcats.category_set_id

FROM mtl_category_sets mcats, mtl_categories mcat

WHERE mcats.category_set_name LIKE '%'

AND mcat.category_id = mcats.default_category_id

ORDER BY 1, 2

/

-- find ALL items assigned TO categories OF a category SET :

SELECT mcats.category_set_name,

mcat.segment1 || '.' || mcat.segment2 CATEGORY, msi.segment1 item,

msi.description item_desc

FROM mtl_item_categories micat,

mtl_category_sets mcats,

mtl_categories mcat,

mtl_system_items_vl msi

WHERE mcats.category_set_name LIKE 'Inv%'

AND micat.category_set_id = mcats.category_set_id

AND micat.category_id = mcat.category_id

AND mcat.segment1 LIKE 'N%'

AND msi.inventory_item_id = micat.inventory_item_id

AND msi.organization_id = micat.organization_id

AND msi.organization_id = 204

ORDER BY 1, 2, 3

/

FIRST LEVEL COMPONENTS OF AN ASSEMBLY


SELECT
bom.assembly_item_id,
bic.component_sequence_id ,
bic.bill_sequence_id ,
--parent_bill_seq_id ,
bic.operation_seq_num ,
bic.component_item_id ,
msi.segment1,
--bic.bom_item_type ,
bic.item_num ,
bic.component_quantity
FROM
bom_inventory_components bic,
mtl_system_items msi,
bom_bill_of_materials bom
WHERE bom.assembly_item_id=149
AND bom.organization_id=207
AND bom.bill_sequence_id=bic.bill_sequence_id
AND bic.component_item_id=msi.inventory_item_id
AND msi.organization_id=207
ORDER BY 1,2
/SELECT
(SELECT msi.segment1 from mtl_system_items msi WHERE msi.inventory_item_id=bom.assembly_item_id AND msi.organization_id=207) parent_item,
bom.assembly_item_id,
lpad(' ',2*(level-1),' ')||
(SELECT msi.segment1 FROM mtl_system_items msi WHERE msi.inventory_item_id=bic.component_item_id AND msi.organization_id=207) child_item,
bic.component_item_id child_item_id,
--bic.component_sequence_id ,
bic.bill_sequence_id ,
--parent_bill_seq_id ,
bic.operation_seq_num ,
--bic.bom_item_type ,
--bic.item_num ,
level,
bic.component_quantity
FROM
bom_inventory_components bic,
(SELECT * FROM bom_bill_of_materials WHERE organization_id=207) bom
WHERE bom.bill_sequence_id=bic.bill_sequence_id
START WITH  bom.assembly_item_id=149
CONNECT BY PRIOR bic.component_item_id=bom.assembly_item_id
--ORDER BY LEVEL, bom.assembly_item_id
/


BOM OPERATION ROUTING


SELECT
--bor.routing_sequence_id ,
bos.operation_sequence_id op_seq_id ,
bos.operation_seq_num op_seq ,
bos.operation_description ,
--bos.standard_operation_id ,
bos.department_id ,
bos.operation_type ,
bor.routing_type ,
bso.operation_code operation_code,
bor.completion_subinventory comp_subinv,
bor.completion_locator_id comp_loc ,
bd.department_code department_code
FROM
bom_standard_operations bso,
bom_departments bd,
bom_operation_sequences bos,
bom_operational_routings bor
WHEREe bor.assembly_item_id = 149
AND bor.organization_id =207
AND bor.routing_sequence_id=bos.routing_sequence_id
AND bos.department_id = bd.department_id
AND bos.standard_operation_id = bso.standard_operation_id (+)
ORDER BY bos.operation_sequence_id
/


BOM ROUTING RESOURCES

SELECT
bor.routing_sequence_id ,
bos.operation_sequence_id op_seq_id,
bos.operation_seq_num op_seq,
bso.operation_code op_code,
bos.operation_description ,
bos.standard_operation_id std_op_id,
bos.department_id dept,
bos.operation_type op_type,
boc.resource_seq_num res_seq,
boc.resource_id ,
boc.activity_id ,
boc.basis_type basis,
br.resource_code res_code,
bor.completion_subinventory comp_subinv,
bor.completion_locator_id comp_loc,
bor.routing_type rout_type,
boc.usage_rate_or_amount rate
FROM
bom_standard_operations bso,
bom_resources br,
bom_operation_resources boc,
bom_operation_sequences bos,
bom_operational_routings bor
WHERE
bor.assembly_item_id = 149
AND bor.organization_id =207
AND bor.routing_sequence_id=bos.routing_sequence_id
AND bos.operation_sequence_id=boc.operation_sequence_id
AND br.resource_id = boc.resource_id
AND bos.standard_operation_id = bso.standard_operation_id (+)
ORDER BY 1
/


FIND ATTACHMENTS FOR BOM 


SELECT to_number(AD.PK1_VALUE) operation_sequence_id,
TL.DESCRIPTION instruction_code,
ST.SHORT_TEXT instruction_description
FROM FND_DOCUMENTS D,
FND_DOCUMENTS_TL TL,
FND_DOCUMENTS_SHORT_TEXT ST,
FND_ATTACHED_DOCUMENTS AD,
bom_operation_sequences bos
WHERE bos.operation_sequence_id =166
and bos.operation_sequence_id = to_number(AD.PK1_VALUE)
AND D.DOCUMENT_ID = TL.DOCUMENT_ID
AND TL.DOCUMENT_ID = AD.DOCUMENT_ID
AND TL.MEDIA_ID = ST.MEDIA_ID
AND SYSDATE BETWEEN TRUNC(NVL(D.START_DATE_ACTIVE, SYSDATE))
AND TRUNC(NVL(D.END_DATE_ACTIVE, SYSDATE))+1
AND AD.ENTITY_NAME like 'BOM_OPERATION_SEQUENCES'
AND TL.LANGUAGE = userenv('LANG')
ORDER BY instruction_code
/

AR AGING REPORT

select customer_number, customer_name,
paymt,
Day_030 ,
Day_031_060,
Day_061_090,
Day_091_120,
Day_121_150,
Day_151_180,
Day_181,
no_ofTrx
from  
(select pay.org_id,pay.customer_id
--        ,pay.trx_number, pay.trx_date
--   ,pay.AMOUNT_DUE_ORIGINAL, pay.AMOUNT_DUE_REMAINING,to_Date ('asofdate') - trunc(pay.trx_date)
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) <=30   then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id)  end ) ) Day_030
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 31  and 60   then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) ) Day_031_060   
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 61  and 90   then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) ) Day_061_090
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 91  and 120  then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) )  Day_091_120   
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 121 and 150  then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) )  Day_121_150
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) between 151 and 180  then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) )  Day_151_180   
, sum((case  when ( to_Date ('&1') - trunc(pay.trx_date)  ) >= 181               then   APPS.XX_ar_aging_amtapp(to_Date ('&1'),pay.customer_trx_id) end ) ) Day_181
,count(*) no_ofTrx
,''
from       apps.ar_payment_Schedules_all pay
where  pay.class in ('XX','YY','ZZ') and pay.org_id = &3
and pay.gl_date  <= to_Date ('&1')
and pay.GL_DATE_CLOSED >  to_Date ('&1')
and exists   ( select 'x' from  apps.gl_code_combinations gl 
,apps.ra_cust_trx_line_gl_dist_all distgl
where  (trim (('&2')) ='0' or gl.segment4 in ('&2'))   --gl.segment4 in ('&2')
and gl.CODE_COMBINATION_ID =  distgl.CODE_COMBINATION_ID
and distgl.ACCOUNT_CLASS ='REC'
and distgl.CUSTOMER_TRX_ID  =pay.CUSTOMER_TRX_ID
)           
group by pay.org_id,pay.customer_id
--   ,pay.AMOUNT_DUE_ORIGINAL, pay.AMOUNT_DUE_REMAINING ,pay.trx_number, pay.trx_date
)  invag ,
(select  pay.org_id,pay.customer_id, sum(receiptL.AMOUNT_APPLIED) paymt
from  apps.ar_payment_Schedules_all pay
,AR_RECEIVABLE_APPLICATIONS_ALL receiptL
where  pay.class ='PMT' and receiptl.status in ('ACC','UNAPP') and pay.org_id =&3
and   pay.PAYMENT_SCHEDULE_ID =     receiptL.PAYMENT_SCHEDULE_ID
and pay.gl_date  <= to_Date ('&1')
and pay.GL_DATE_CLOSED >  to_Date ('&1')
group by pay.org_id,pay.customer_id  
) payment,
apps.ra_customers cust
where cust.customer_id =  invag.customer_id (+)
and   cust.customer_id =  payment.customer_id (+)
and  (invag.org_id is not null or payment.org_id is not null) 
/