Friday, 6 January 2017

PO,AP_INVOICE_ALL CODING IN ORACLE APPS



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