Tuesday, March 29, 2011

IMP 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, -- <>
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)

0 comments:

About This Blog

  © Blogger templates The Professional Template by Ourblogtemplates.com 2008

Back to TOP