IMPORTANT APPS QUERIES

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)  
/