PO HEADERS
------------------
select
name ,
ph.segment1,
asp.VENDOR_NAME,
HL.LOCATION_CODE,
PAPF.FULL_NAME,
ph.CREATION_DATE,
PLC.DESCRIPTION,
ASSA.VENDOR_SITE_CODE,
HLA.LOCATION_CODE,
ph.AUTHORIZATION_STATUS,
ASCS.LAST_NAME||','||ASCS.FIRST_NAME,
ph.CURRENCY_CODE
from po_headers_all ph,
hr_operating_units hr,
AP_SUPPLIERS asp,
HR_LOCATIONS_ALL HL,
PER_ALL_PEOPLE_F PAPF,
PO_LOOKUP_CODES PLC,
AP_SUPPLIER_SITES_ALL ASSA,
HR_LOCATIONS_ALL HLA,
AP_SUPPLIER_CONTACTS ASCS
where ph.org_id=hr.organization_id
AND ph.segment1='6040' and
asp.vendor_id=ph.vendor_id
AND HL.SHIP_TO_LOCATION_ID=ph.SHIP_TO_LOCATION_ID
AND PAPF.PERSON_ID=ph.AGENT_ID
AND PLC.LOOKUP_CODE='STANDARD'
AND ph.TYPE_LOOKUP_CODE=PLC.LOOKUP_CODE
AND PLC.LOOKUP_TYPE='PO TYPE'
AND
ASSA.VENDOR_SITE_ID=ph.VENDOR_SITE_ID
AND
HLA.LOCATION_ID=ph.BILL_TO_LOCATION_ID
AND
ASCS.VENDOR_CONTACT_ID=ph.VENDOR_CONTACT_ID
PO_LINES:
--------------
select
PLT.LINE_TYPE,
MSI.SEGMENT1,
MC.SEGMENT1||','||MC.SEGMENT2,
MSI.DESCRIPTION,
PLA.UNIT_MEAS_LOOKUP_CODE AS UOM,
PLA.QUANTITY,
PLA.UNIT_PRICE,
PLA.UNIT_PRICE*PLA.QUANTITY AS AMOUNT
FROM PO_LINES_ALL PLA,
PO_LINE_TYPES PLT,
MTL_SYSTEM_ITEMS_B MSI,
MTL_CATEGORIES MC
WHERE PLA.LINE_TYPE_ID=PLT.LINE_TYPE_ID
AND PO_HEADER_ID='110566'
AND PLA.ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=PLA.ORG_ID
AND MC.CATEGORY_ID=PLA.CATEGORY_ID
SHIPMENT:
---------------
SELECT
OD.ORGANIZATION_CODE,
HL.LOCATION_CODE,
PLL.NEED_BY_DATE
FROM
ORG_ORGANIZATION_DEFINITIONS OD,
PO_LINE_LOCATIONS_ALL PLL,
HR_LOCATIONS_ALL HL
WHERE OD.ORGANIZATION_ID=PLL.ORG_ID
AND PO_LINE_ID='173455'
AND HL.SHIP_TO_LOCATION_ID=PLL.SHIP_TO_LOCATION_ID

select
rt.quantity,
rsl.unit_of_measure,
rsl.item_description,
rsl.DESTINATION_TYPE_CODE ,
(select distinct segment1 from mtl_system_items_b msit where msit.inventory_item_id=rsl.item_id)item_name,
rsl.SHIPMENT_LINE_STATUS_CODE ,
(select hr.location_code from hr_locations_all hr,po_line_locations_all pl where pl.line_location_id=rsl.PO_LINE_LOCATION_ID and hr.location_id=pl.ship_to_location_id) location_code,
(select full_name from per_all_people_f papf where papf.person_id=rsl.employee_id)emp_name,
rsl.TO_SUBINVENTORY,
(select segment1||'.'||segment2 from mtl_categories where CATEGORY_ID=rsl.CATEGORY_ID)catogary
(select Name from hr_operating_units where organization_id=rsl.DELIVER_TO_LOCATION_ID)unit_name,
(select asp.vendor_name from ap_suppliers asp where asp.vendor_id=rt.vendor_id)supplier,
(select hr.location_code from hr_locations_all hr,po_line_locations_all pl where pl.line_location_id=rsl.PO_LINE_LOCATION_ID and hr.location_id=pl.ship_to_location_id)||','||
(select
full_name from
per_all_people_f papf where papf.person_id = rsl.employee_id)||','|| rsl.TO_SUBINVENTORY destination,
(select DISPLAYED_FIELD from po_headers_all po,po_lookup_codes pc where po.po_header_id=rsl.po_header_id and pc.lookup_code=po.type_lookup_code and pc.lookup_type='PO TYPE') order_type,
(select routing_name from rcv_routing_headers rrh whererouting_header_id=rt.routing_header_id)routing_name,
po.segment1,
rsh.receipt_num
from
rcv_transactions rt,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
po_headers_all po
where 1=1
and rt.transaction_type='RECEIVE'
and rt.shipment_header_id=rsl.shipment_header_id
and rsh.shipment_header_id=rsl.shipment_header_id
and rsh.ship_to_org_id='204'
--and
rsh.receipt_num='8464'
and po.po_header_id=rsl.po_header_id
and po.segment1='6040'---po number
2way,3way,4way
=============
select inspection_required_flag||receipt_required_flag, decode(inspection_required_flag||receipt_required_flag,'NN','2 Way','NY','3 Way','YY','4 Way') match
from po_line_locations_all where decode(inspection_required_flag||receipt_required_flag,'NN','2 Way','NY','3 Way','YY','4 Way')='2 Way'
AP_INVOICE_ALL:

HEADERS:
select hou.name,
aia.invoice_num,
aia.INVOICE_TYPE_LOOKUP_CODE,
hz.party_name,
segment1 supp_num,
assa.vendor_site_code,
aia.invoice_date,
aia.invoice_currency_code,
aia.invoice_amount,
aia.total_tax_amount,
aia.gl_date,
aia.payment_currency_code,
aia.PAYMENT_CROSS_RATE_DATE,
ads.distribution_set_name,
flv.meaning,
aia.terms_date,
at.name,
(select payment_method_name from iby_payment_methods_tl ipm where aia.PAYMENT_METHOD_CODE=ipm.PAYMENT_METHOD_CODE) payment_method_name
,assa.pay_group_lookup_code,
aia.taxation_country,
aia.self_assessed_tax_amount,
aia.remit_to_supplier_name,
aia.remit_to_supplier_site,
(select bank_account_name from iby_ext_bank_accounts ieba where ieba.ext_bank_account_id=aia.external_bank_account_id) bank_account_name,
(select masked_bank_account_num from iby_ext_bank_accounts ieba where ieba.ext_bank_account_id=aia.external_bank_account_id) account_number
from ap_invoices_all aia,
hr_operating_units hou,
ap_suppliers ass,
hz_parties hz,
ap_supplier_sites_all assa,
ap_distribution_sets_all ads,
fnd_lookup_values flv,
ap_terms at
where invoice_num='RT1222343129'
and aia.org_id=hou.ORGANIZATION_ID
and aia.party_id=hz.party_id
and ass.vendor_id=aia.vendor_id
and assa.VENDOR_SITE_ID=aia.vendor_site_id
and ass.vendor_id=assa.vendor_id
and aia.distribution_set_id=ads.distribution_set_id(+)
and flv.lookup_type = 'INVOICE MATCH OPTION'
AND flv.lookup_code = assa.match_option
and at.term_id=aia.terms_id
select * from all_tab_columns where column_name like
'%SUBTOTAL%'
select * from fnd_territories_tl where territory_code='US';
select * from iby_payment_methods_tl where payment_method_code='EFT';
select * from iby_ext_bank_accounts where bank_account_name='BofA-EDI-Vision Operations';
LINES:
---------
select LINE_TYPE_LOOKUP_CODE,AMOUNT,DESCRIPTION,final_match_flag,
tax_regime_code,tax,tax_jurisdiction_code,
tax_status_code,tax_rate,type_1099,income_tax_region,wfapproval_status,
NVL(ap_invoice_lines_utility_pkg.get_approval_status(ail.invoice_id,ail.line_number),'NEVER APPROVED' )
validation_status
,(SELECT distinct
full_name
FROM apps.per_all_people_f
papf
WHERE
papf.person_id =
ail.requester_id
AND
TRUNC (SYSDATE)
BETWEEN trunc(
papf.effective_start_date
)
AND trunc(
papf.effective_end_date
))requester_name
from ap_invoice_lines_all ail
where invoice_id='211260';
TAX LINE SUMMARTY:

select
zl.tax_regime_code,zl.tax_rate,zl.tax_amt
from
zx_lines zl,
ap_invoices_all aia
where aia.invoice_id=zl.trx_id and aia.invoice_id='211260'
ZX TABLES:
------------------
desc zx_lines_summary_v
zx_lines_summary
zx_lines_v
zx_rates_tl
zx_lines ,
zx_regimes_tl
zx_jurisdictions_tl
zx_taxes_tl
zx_status_tl
desc ap_invoices_v
select * from ap_invoices_all where
invoice_id='211260';
select * from zx_lines where trx_id='211260';
select * from zx_rates_tl where trx_id='211260';
AP_INVOICE_HOLDS
select alc.displayed_field,
aha.hold_reason,
aha.hold_date,
DECODE (aha.held_by,
5, (SELECT alc5.displayed_field
FROM ap_lookup_codes alc5
WHERE alc5.lookup_type(+) = 'NLS TRANSLATION'
AND alc5.lookup_code(+) = 'SYSTEM'),
fu.user_name
) held_by_user_name,
(select displayed_field from ap_lookup_codes al where aha.RELEASE_LOOKUP_CODE=al.LOOKUP_CODE)relaease_name,
aha.release_reason,
DECODE (aha.last_updated_by,
5, (SELECT alc6.displayed_field
FROM ap_lookup_codes alc6
WHERE alc6.lookup_type(+) = 'NLS TRANSLATION'
AND alc6.lookup_code(+) = 'SYSTEM'),
fu.user_name
)released_by,
aha.last_update_date
from
ap_invoices_all
aia ,
ap_holds_all
aha,
ap_lookup_codes
alc,
fnd_user
fu
where
aia.invoice_num='20-MAR-2008' and aia.org_id='204'
and aha.hold_lookup_code=alc.lookup_code
and aia.invoice_id=aha.invoice_id
AND aha.held_by = fu.user_id
No comments:
Post a Comment