[E-Bussiness Suite] Query PR which don't have PO R12

Description
    this is query to display PR with details line ,Cost center, last approver (action history) ,  which don't have PO, you can costumize this query that can display all PR which have or Not a PO.

How to 

select distinct prha.segment1 as PR_NUM
       ,prla.LINE_NUM
       ,gcc.SEGMENT3 as Cost_Center
       ,prla.ITEM_DESCRIPTION
       ,prla.QUANTITY
       ,prla.UNIT_PRICE
       ,prla.UNIT_PRICE * prla.QUANTITY as Total_Amount
       ,pha.SEGMENT1 as PO_NUM
       ,prha.CREATION_DATE as Tanggal_Buat_PR
       ,reqah.full_name req_last_approver
       ,reqah.action_date "Date PR Approver"
       ,prha.AUTHORIZATION_STATUS STATUS_PR
FROM    po.po_requisition_headers_all prha
        , po.po_requisition_lines_all prla
        , po.po_line_locations_all plla
        , po.po_lines_all pla
        , po_headers_all PHA
        , gl.gl_code_combinations gcc
        , po.po_req_distributions_all prda
        , (SELECT prha.requisition_header_id
           ,papf.full_name
           ,pah.action_code
           ,pah.object_id
           ,pah.ACTION_DATE
           FROM po.po_action_history pah
                      , po.po_requisition_headers_all prha
                      , applsys.fnd_user fu
                      , hr.per_all_people_f papf
                  WHERE prha.ORG_ID = '86'
                    --AND prha.segment1 = '8880'
                    AND pah.object_id = prha.requisition_header_id
                    AND pah.employee_id = fu.employee_id
                    AND fu.employee_id = papf.person_id
                    AND SYSDATE BETWEEN papf.effective_start_date
                                    AND papf.effective_end_date
                    AND pah.object_type_code = 'REQUISITION'
                    AND pah.action_code = 'APPROVE'
                    AND pah.sequence_num =
                           (SELECT MAX(sequence_num)
                              FROM po.po_action_history pah1
                             WHERE pah1.object_id = pah.object_id
                               AND pah1.object_type_code = 'REQUISITION'
                               AND pah1.action_code = 'APPROVE')) reqah
WHERE prha.ORG_ID = '86'
AND prha.AUTHORIZATION_STATUS = 'APPROVED'
--AND prha.segment1 = '8880'
AND prha.creation_date >= to_date('1/1/2014','dd/mm/yyyy')
AND prha.creation_date <= to_date('12/8/2014','dd/mm/yyyy')
AND prha.requisition_header_id = prla.requisition_header_id
--linking to action history (reqah) based on header PR / PO
AND reqah.requisition_header_id = prha.requisition_header_id
--distribution always linking to Line ID
AND prda.REQUISITION_LINE_ID = prla.REQUISITION_LINE_ID
--if line location is Null then the PO doesn't exist, if exist then PO exist too.
AND plla.line_location_id is null
AND prla.line_location_id = plla.line_location_id(+)
AND plla.po_header_id = pla.po_header_id(+)
AND pla.po_header_id = pha.po_header_id(+)
AND gcc.CODE_COMBINATION_ID = prda.CODE_COMBINATION_ID
-- select your cost center , this is optional you can comment it if you want display all cost center
AND gcc.SEGMENT3 in ('000','001')
order by prha.segment1,prla.line_num, gcc.SEGMENT3 asc

Read more

[E-Bussiness Suite] Query Invoice Prepayment with Accountability Invoice

Deskripsi
      To display Accountability Invoice based on Invoice Prepayment, you can modified the filter.

Howto
select DISTINCT aia1.invoice_num
                ,pv.VENDOR_NAME
                ,aia1.amount_paid
                ,aia1.DESCRIPTION as Deskripsi_Prepayment
                ,aia1.GL_DATE as GL_Prepayment
                ,aia2.invoice_num as Invoice_PJ
                ,aia2.DESCRIPTION as Deskripsi_PJ
                ,(aida2.AMOUNT * -1) as Amount_PJ
                ,aia2.GL_DATE as GL_PJ
             
from ap_invoices_all aia1
    ,ap_invoice_distributions_all aida1
    ,ap_invoices_all aia2
    ,ap_invoice_distributions_all aida2
    ,po_vendors PV
 
where aia1.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
and aia1.GL_DATE >= to_date('01/01/2013','DD/MM/YYYY')
and aia1.GL_DATE <= to_date('31/12/2013','DD/MM/YYYY')
--and aia1.INVOICE_NUM = 'xxx'
--and aia1.VENDOR_ID = 'xxx'
and aia1.INVOICE_ID(+) = aida1.INVOICE_ID
and pv.VENDOR_ID = aia1.VENDOR_ID
and (aida1.REVERSAL_FLAG is null or aida1.REVERSAL_FLAG = 'N')
and aida2.PREPAY_DISTRIBUTION_ID(+) = aida1.INVOICE_DISTRIBUTION_ID
and aia2.INVOICE_ID(+) = aida2.INVOICE_ID
and (aida2.REVERSAL_FLAG is null or aida2.REVERSAL_FLAG = 'N')
--and aia2.vendor_id = 'xxx'
order by to_date(aia1.GL_DATE,'DD/MM/YYYY'),aia1.INVOICE_NUM;

Read more

[E-Bussiness Suite] Query Data Vendor (Supplier)

Deskripsi
     Display all data master vendor (supplier)

Howto
1. Running Show Hidden View , here this link 
2. Running Query below :
select distinct pvs.ORG_ID
               ,pv.vendor_name
               ,pv.VENDOR_NAME_ALT
               ,pvs.ADDRESS_LINE1
               ,pvs.ADDRESS_LINE2
               ,pvs.VENDOR_SITE_CODE
               ,pvs.CITY
               ,pvs.ZIP
               ,pvs.PROVINCE
               ,pvs.PAYMENT_METHOD_LOOKUP_CODE
               ,pvs.TERMS_DATE_BASIS
               ,pvs.VAT_CODE
               ,pvs.VAT_REGISTRATION_NUM
               ,pvs.DISTRIBUTION_SET_ID
               ,(gcc.SEGMENT1 || '.' || gcc.SEGMENT2 || '.' || gcc.SEGMENT3 || '.' || gcc.segment4) as ACCTS_PAY_CODE_COMBINATION
               ,(gcc2.SEGMENT1 || '.' || gcc2.SEGMENT2 || '.' || gcc2.SEGMENT3 || '.' || gcc2.segment4) as PREPAY_CODE_COMBINATION
               ,pvs.ACCTS_PAY_CODE_COMBINATION_ID
               ,pvs.PREPAY_CODE_COMBINATION_ID
               ,pvs.TERMS_ID
               ,pvs.ALLOW_AWT_FLAG
               ,pvs.PURCHASING_SITE_FLAG
               ,pvc.first_name
               ,pvc.last_name
               ,pvc.prefix
               ,pvc.title
               ,pvc.area_code
               ,pvc.phone
               ,pvc.contact_name_alt
               ,pvc.email_address
               ,pvc.alt_area_code
               ,pvc.fax

from po_vendors PV,
     po_vendor_sites PVS,
     po_vendor_contacts PVC,
     gl_code_combinations GCC,
     gl_code_combinations GCC2
   
 
where pv.VENDOR_ID(+) = pvs.VENDOR_ID
and pv.VENDOR_ID = pvc.vendor_contact_id(+)
and pvs.ACCTS_PAY_CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID(+)
and pvs.PREPAY_CODE_COMBINATION_ID = gcc2.CODE_COMBINATION_ID(+)
--and pvs.org_id = 0
order by vendor_name asc;

Read more

[E-Bussiness Suite] Query PR - PO - Vendor

Deskripsi
    Untuk mendapatkan data PR - PO + PO Amount dan Nama Vendor berdasarkan range date create PR dan ID Vendor

Howto
select distinct prha.SEGMENT1 as PR
               ,prha.DESCRIPTION as PR_DESC
               ,prha.AUTHORIZATION_STATUS as PR_STATUS
               ,pha.SEGMENT1 as PO
               ,PO_AMOUNT.amount as PO_Amount
               ,pha.AUTHORIZATION_STATUS as PO_STATUS
               ,pv.VENDOR_NAME
               ,prha.CREATION_DATE as PR_DATE
             
from  po_requisition_headers_all PRHA
     ,po_requisition_lines_all PRLA
     ,po_line_locations_all PLLA
     ,po_headers_all PHA
     ,po_lines_all pla
     ,po_vendors PV
     ,(select  PHA.po_header_id
              ,SUM(PLA.UNIT_PRICE*PLA.QUANTITY) as Amount
              ,pv.vendor_id
       from PO_HEADERS_ALL PHA
           ,PO_LINES_ALL PLA
           ,po_vendors PV
       where PHA.po_header_id = PLA.po_header_id
       and pha.VENDOR_ID = pv.VENDOR_ID
       and pv.VENDOR_ID = :VENDOR_ID
       group by PHA.po_header_id,pv.vendor_id) PO_AMOUNT

where prha.CREATION_DATE >= to_date('01/01/2011','DD/MM/YYYY')
and prha.CREATION_DATE <= to_date('31/12/2013','DD/MM/YYYY')
and prha.REQUISITION_HEADER_ID = prla.REQUISITION_HEADER_ID(+)
and prla.LINE_LOCATION_ID = plla.LINE_LOCATION_ID(+)
and plla.PO_LINE_ID = pla.PO_LINE_ID(+)
and pla.PO_HEADER_ID = pha.PO_HEADER_ID(+)
and pha.PO_HEADER_ID = PO_AMOUNT.po_header_id
and pha.VENDOR_ID = pv.VENDOR_ID
and pv.VENDOR_ID = :VENDOR_ID

group by prha.SEGMENT1
        ,po_amount.amount
        ,prha.DESCRIPTION
        ,prha.AUTHORIZATION_STATUS
        ,pha.SEGMENT1
        ,pha.AUTHORIZATION_STATUS
        ,pv.VENDOR_NAME
        ,prha.CREATION_DATE

Read more

[E-Bussiness Suite] PR link to Master Item and GL

Deskripsi
     PR - Master Item - GL , based on PR Line and Distribution.

Howto
select distinct --PRHA.REQUISITION_HEADER_ID
              --, prla.line_num
              --, prla.item_description
                msif.segment1 as MASTER_ITEM
              , msif.DESCRIPTION as DESKRIPSI_ITEM
              , (gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4) as COA
           
from po_requisition_headers_all PRHA
    ,po_requisition_lines_all PRLA
    ,po_req_distributions_all PRDA
    ,MTL_SYSTEM_ITEMS_FVL MSIF
    ,GL_CODE_COMBINATIONS GCC
   
where prha.CREATION_DATE >= to_date('01/01/2013','DD/MM/YYYY')
and prha.CREATION_DATE <= to_date('31/12/2013','DD/MM/YYYY')
--and prha.segment1 = 'PR NUMBER'
and prha.requisition_header_id = PRLA.requisition_header_id
and prla.REQUISITION_LINE_ID = prda.REQUISITION_LINE_ID
and prla.ITEM_ID = msif.INVENTORY_ITEM_ID
and gcc.CODE_COMBINATION_ID = prda.CODE_COMBINATION_ID
order by msif.segment1 asc

Read more

[E-Bussiness Suite] Query Invoice Details AP

Deskripsi
     Untuk menampilkan nama vendor , deskripsi , no_pajak , invoice number , dpp , ppn 10% , tanggal invoice , tanggal faktur.

Howto
select pv.VENDOR_NAME as Nama_PT
      ,ai.DESCRIPTION as Deskripsi
      ,data_tax.ATTRIBUTE1 as No_Pajak
      ,ai.INVOICE_NUM as Invoice_Number
      ,data_item.amount as DPP
      ,data_tax.amount as PPN
      ,to_date(ai.INVOICE_DATE,'DD/MM/YYYY') as Tanggal_Invoice
      ,data_tax.ATTRIBUTE2 as Tanggal_Faktur
      --,aid.INVOICE_ID
from ap_invoices_all ai
    ,po_vendors pv
    ,(
        select aid.INVOICE_ID, aid.attribute1, aid.attribute2, aid.AMOUNT
        from ap_invoice_distributions aid
        where aid.LINE_TYPE_LOOKUP_CODE = 'TAX'
     ) data_tax
    ,(
        select aid.INVOICE_ID, aid.AMOUNT
        from ap_invoice_distributions aid
        where aid.LINE_TYPE_LOOKUP_CODE = 'ITEM'
     ) data_item

where ai.GL_DATE >= to_date('01/08/2013','DD/MM/YYYY')
and ai.GL_DATE <= to_date('31/08/2013','DD/MM/YYYY')
and ai.INVOICE_NUM = '1130144'
and ai.VENDOR_ID = pv.VENDOR_ID
and ai.INVOICE_ID = data_tax.INVOICE_ID
and ai.INVOICE_ID = data_item.INVOICE_ID
;

Read more

[E-Bussiness Suite] Query Receivable

Deskripsi
     Untuk mengambil data Receivable (AR) berupa nomor invoice , no faktur pajak , revenue ammount , 10% ppn dari revenue ammount , dan gl_date berdasarkan nomor invoice

Howto
1. Run this query Here
2. Run query below
select RCTA.TRX_NUMBER as Invoice_Number
      ,RCTA.ATTRIBUTE14 as Faktur_Pajak
      ,HP.PARTY_NAME as Nama_PT
      --,sum(extended_amount) as extended_ammount
      ,sum(revenue_amount) as revenue_ammount
      ,sum(10/100*revenue_amount) as PPN_Ammount
      ,GD.GL_DATE as GL_Invoice
from ra_customer_trx_all RCTA
    ,ra_customer_trx_lines RCTL
    ,hz_cust_accounts HCA
    ,hz_parties HP
    ,ra_cust_trx_line_gl_dist GD
where RCTA.TRX_NUMBER = '3670174'
and line_type = 'LINE'
and RCTA.CUSTOMER_TRX_ID = RCTL.customer_trx_id
and RCTA.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
and HCA.PARTY_ID = HP.PARTY_ID
and RCTA.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID
and 'REC' = gd.account_class
and 'Y' = gd.latest_rec_flag
group by RCTA.customer_trx_id  
        ,RCTA.ATTRIBUTE14
        ,HP.PARTY_NAME
        ,GD.GL_DATE
        ,RCTA.TRX_NUMBER
;

*Note : Field attribute14 as No_Pajak / Tax_Number , maybe vary based on oracle settings
     

Read more

[E-Bussiness Suite] Query Master Item with Expense Account

Deskripsi
     Berikut adalah query untuk menarik Master item beserta detail deskripsi dan Expense Account.

Howto
SELECT MSIF.segment1 as Master_Items,
               MSIF.description as Deskripsi,
               GCCK.CONCATENATED_SEGMENTS as Expense_Account,
               MSIF.CREATION_DATE
FROM MTL_SYSTEM_ITEMS_FVL MSIF,
             GL_CODE_COMBINATIONS_KFV GCCK  
WHERE GCCK.CODE_COMBINATION_ID = MSIF.EXPENSE_ACCOUNT
ORDER BY MSIF.SEGMENT1 ASC;

Read more

[E-Bussiness Suite] Background Engine Down

Deskripsi
     Jika Backgroud Engine Oracle E-Bussiness Suite statusnya 'Down' , maka bisa kembali di 'Up' melalui user account setup (not sysadmin).

How To
     1. After login Admin, Goto System Administrator > Requests
     2. Pilih Request Workflow Background Process.
     3. Isi Parameter :
         Default : - Process Deffered : Yes
                      - Process Timeout : Yes
                      - Process Stuck : Yes
     4. Submit Request and done.
     5. Background Engine harusnya sudah 'UP' setelah process running request selesai.


Berikut adalah penjelasan parameter , based on oracle documents :

Item Type	Specify an item type to restrict this engine to activities associated with that item type. If you do not specify an item type, the engine processes any deferred activity regardless of its item type.
Minimum Threshold	Specify the minimum cost that an activity must have for this background engine to execute it, in hundredths of a second.
Maximum Threshold	Specify the maximum cost that an activity can have for this background engine to execute it, in hundredths of a second.
	By using Minimum Threshold and Maximum Threshold you can create multiple background engines to handle very specific types of activities. The default values for these arguments are null so that the background engine runs activities regardless of cost.
Process Deferred	Specify whether this background engine checks for deferred activities. Setting this parameter to 'Yes' allows the engine to check for deferred activities.
Process Timeout	Specify whether this background engine checks for activities that have timed out. Setting this parameter to 'Yes' allows the engine to check for timed out activities.
Process Stuck	Specify whether this background engine checks for stuck processes. Setting this parameter to 'Yes' allows the engine to check for stuck processes.

Read more

[E-Bussiness Suite] Query Creation Date PR - PO - RR

Deskripsi
     Menampilkan data PR - PO - RR beserta Creation Datenya dan difilter berdasarkan tanggal pembuatan PR , Status PR Approved , dan employee yang membuat (Preparer)

How To
1. Jalan Query Show Hidden View , article bisa dilihat DISINI
2. Jalankan Query dibawah ini :
select distinct PRHA.segment1 as PR
               ,PRHA.CREATION_DATE as CREATE_PR
               ,PRHA.DESCRIPTION as Deskripsi_PR
               ,PRLA.LINE_NUM as LINE
               ,PRLA.ITEM_DESCRIPTION as DESKRIPSI_LINE_PR
               ,PRHA.APPROVED_DATE as Approved_PR
               ,PHA.segment1 as PO
               ,PHA.CREATION_DATE as CREATE_PO
               ,PHA.APPROVED_DATE as Approved_PO
               ,RVHV.RECEIPT_NUM as RECEIPT_NUMBER
               ,RVHV.CREATION_DATE as CREATE_RECEIPT
       
from po_requisition_headers_all PRHA
       ,po_requisition_lines_all PRLA
       ,po_line_locations_all PLLA
       ,po_headers_all PHA
       ,po_lines_all PLA
       ,RCV_SHIPMENT_LINES RSL
       ,RCV_VRC_HDS_V RVHV

where prha.creation_date >= to_date('1/1/2013','dd/mm/yyyy')
and prha.requisition_header_id = PRLA.requisition_header_id(+)
and PRLA.line_location_id = PLLA.line_location_id(+)
and PHA.po_header_id(+) = PLA.po_header_id
and PLA.po_line_id(+) = plla.po_line_id
AND PHA.po_header_id = RSL.po_header_id (+)
AND RSL.shipment_header_id = RVHV.shipment_header_id(+)
AND PRHA.AUTHORIZATION_STATUS = 'APPROVED'
AND (PRHA.PREPARER_ID = 646 OR PRHA.PREPARER_ID =743 )

order by PRHA.CREATION_DATE , PRLA.LINE_NUM as LINE

*NOTE :
Untuk mendapatkan list dari PREPARER / EMPLOYEE (Preparer_ID), bisa didapat dari table PER_PEOPLE_F (Person_ID).

Read more

[E-Bussiness Suite] Query Invoice with Accounting by Date

Deskripsi
     Query Invoice with Accounting (Debit,Credit,COA,Liabilities) plus additional display related PR , PO , RR , Invoice , Voucher Payment (STATUS : Negotiable and Reconciled Unaccounted) search by Invoice GL_DATE.

Howto
1. Execute Show View Hidden Here , because we need some oracle view.
2. Run Query
select *
from
(
select distinct PRHA.segment1 as PR
                    , PHA.segment1 as PO
                    , pv.VENDOR_NAME as Supplier
                    ,RVHV.RECEIPT_NUM as RECEIPT_NUMBER
                   ,AI.INVOICE_NUM as INVOICE
                   ,AC.DOC_SEQUENCE_VALUE as VOUCHER_PAYMENT
                   --,AID.PA_ADDITION_FLAG
                   --,AID.PROJECT_ACCOUNTING_CONTEXT
                  ,AID.DISTRIBUTION_LINE_NUMBER as DIST_LINE_NUM
                  ,AID.DESCRIPTION
                  ,AID.AMOUNT
                  ,AELA.AE_LINE_TYPE_CODE
                  ,GCC.SEGMENT1 AS ACCOUNT
                  ,AELA.ACCOUNTED_DR
                  ,AELA.ACCOUNTED_CR
     
from ap_invoices_all ai
    ,AP_INVOICE_PAYMENTS_V AIP
    ,AP_CHECKS_all AC
    ,ap_invoice_distributions AID
    ,po_distributions PD
    ,po_headers_all PHA
    ,po_line_locations_all PLLA
    ,po_lines_all PLA
    ,po_requisition_headers_all PRHA
    ,po_requisition_lines_all PRLA
    ,RCV_VRC_HDS_V RVHV
    ,RCV_SHIPMENT_LINES RSL
    ,ap_ae_lines_all aela
    ,gl_code_combinations GCC
    ,po_vendors PV
 
where ai.gl_date >= to_date('01/05/2013','dd/mm/yyyy')
and ai.gl_date <= to_date('02/05/2013','dd/mm/yyyy')
and ai.org_id = '0'
AND AI.INVOICE_ID = AIP.INVOICE_ID(+)
AND AIP.CHECK_ID = AC.CHECK_ID(+)
AND AI.INVOICE_ID = AID.INVOICE_ID(+)
AND AID.po_distribution_id = PD.po_distribution_id(+)
AND PD.po_header_id = PHA.po_header_id(+)
and PHA.po_header_id = PLA.po_header_id(+)
and PLA.po_line_id = plla.po_line_id(+)
and prha.requisition_header_id(+) = PRLA.requisition_header_id
and PRLA.line_location_id(+) = PLLA.line_location_id
AND PHA.po_header_id = RSL.po_header_id (+)
AND RSL.shipment_header_id = RVHV.shipment_header_id(+)
AND AELA.SOURCE_ID = AID.INVOICE_DISTRIBUTION_ID
AND GCC.CODE_COMBINATION_ID = AELA.CODE_COMBINATION_ID
AND PHA.VENDOR_ID = PV.VENDOR_ID(+)
AND (AC.STATUS_LOOKUP_CODE = 'RECONCILED UNACCOUNTED' OR AC.STATUS_LOOKUP_CODE = 'NEGOTIABLE')
--AND AID.TAX_RECOVERABLE_FLAG = 'N'
--AND AID.LINE_TYPE_LOOKUP_CODE <> 'TAX'
--AND AID.FINAL_MATCH_FLAG is null
--AND AID.DISTRIBUTION_LINE_NUMBER = '2'
UNION ALL
select distinct NULL as PR
               ,NULL as PO
               ,NULL as Supplier
               ,NULL as RECEIPT_NUMBER
               ,ai.INVOICE_NUM as INVOICE
               ,NULL as VOUCHER_PAYMENT
               ,AELA.AE_LINE_NUMBER as DIST_LINE_NUM
               ,NULL
               ,NULL
               ,AELA.AE_LINE_TYPE_CODE
               ,GCC.SEGMENT1 AS ACCOUNT
               ,AELA.ACCOUNTED_DR
               ,AELA.ACCOUNTED_CR
             
from AP_INVOICES_ALL AI
    ,AP_AE_LINES_ALL AELA
    ,gl_code_combinations GCC
 
where ai.gl_date >= to_date('01/05/2013','dd/mm/yyyy')
and ai.gl_date <= to_date('02/05/2013','dd/mm/yyyy')
and aela.SOURCE_ID = ai.INVOICE_ID(+)
and ae_line_type_code = 'LIABILITY'
and aela.ACCOUNTED_CR is not null
AND GCC.CODE_COMBINATION_ID = AELA.CODE_COMBINATION_ID
) data

order by 5,7

Read more

[E-Bussiness Suite] Query Get Accounting 'Liabilities' invoice

Deskripsi
     Untuk mendapatkan data 'Liabilities' pada View Accounting (Invoices)

Howto
select distinct ai.INVOICE_NUM
               ,AELA.AE_LINE_TYPE_CODE
               ,AELA.AE_LINE_NUMBER
               ,GCC.SEGMENT1 AS ACCOUNT
               ,AELA.ACCOUNTED_DR as debit
               ,AELA.ACCOUNTED_CR as credit

             
from AP_INVOICES_ALL AI
    ,AP_AE_LINES_ALL AELA
    ,gl_code_combinations GCC
 
where ai.gl_date >= to_date('01/05/2013','dd/mm/yyyy')
and ai.gl_date <= to_date('02/05/2013','dd/mm/yyyy')
and aela.SOURCE_ID = ai.INVOICE_ID(+)
and ae_line_type_code = 'LIABILITY'
and aela.ACCOUNTED_CR is not null
AND GCC.CODE_COMBINATION_ID = AELA.CODE_COMBINATION_ID
order by AI.INVOICE_NUM
;

Read more

[E-Bussiness Suite] Query Invoice - Line Distribution with Accounting

Deskripsi
     Untuk menampilkan data PR - PO - RR - Invoice - Payment dan difilter bedasarkan GL Invoice serta ditambah data tambahan dari invoice seperti line_distribution , amount , debit , credit ,  Chart of Account tapi tidak menampilkan 'Liabilities'

Howto
1. Activate Show View Hidden
2. Query this sql below

select distinct PRHA.segment1 as PR
              , PHA.segment1 as PO
              ,RVHV.RECEIPT_NUM as RECEIPT_NUMBER
              ,AI.INVOICE_NUM as INVOICE
              ,AC.DOC_SEQUENCE_VALUE as VOUCHER_PAYMENT  
              --,AID.PA_ADDITION_FLAG
              --,AID.PROJECT_ACCOUNTING_CONTEXT 
              ,AID.DISTRIBUTION_LINE_NUMBER
              ,AID.DESCRIPTION
              ,AID.AMOUNT
              ,AELA.AE_LINE_TYPE_CODE
              ,GCC.SEGMENT1 AS ACCOUNT
              ,AELA.ACCOUNTED_DR
              ,AELA.ACCOUNTED_CR  
       
       
from ap_invoices_all ai
    ,AP_INVOICE_PAYMENTS_V AIP
    ,AP_CHECKS_all AC
    ,ap_invoice_distributions AID
    ,po_distributions PD
    ,po_headers_all PHA
    ,po_line_locations_all PLLA
    ,po_lines_all PLA
    ,po_requisition_headers_all PRHA
    ,po_requisition_lines_all PRLA
    ,RCV_VRC_HDS_V RVHV
    ,RCV_SHIPMENT_LINES RSL
    ,ap_ae_lines_all aela
    ,gl_code_combinations GCC
    
-- change your date here , or you can create input     
where ai.gl_date >= to_date('01/05/2013','dd/mm/yyyy')
and ai.gl_date <= to_date('02/05/2013','dd/mm/yyyy')
and ai.org_id = '0'
AND AI.INVOICE_ID = AIP.INVOICE_ID(+)
AND AIP.CHECK_ID = AC.CHECK_ID(+)
AND AI.INVOICE_ID = AID.INVOICE_ID(+)
AND AID.po_distribution_id = PD.po_distribution_id(+)
AND PD.po_header_id = PHA.po_header_id(+)
and PHA.po_header_id = PLA.po_header_id(+)
and PLA.po_line_id = plla.po_line_id(+)
and prha.requisition_header_id(+) = PRLA.requisition_header_id
and PRLA.line_location_id(+) = PLLA.line_location_id
AND PHA.po_header_id = RSL.po_header_id (+)
AND RSL.shipment_header_id = RVHV.shipment_header_id(+)
AND AELA.SOURCE_ID = AID.INVOICE_DISTRIBUTION_ID 
AND GCC.CODE_COMBINATION_ID = AELA.CODE_COMBINATION_ID
--AND AID.TAX_RECOVERABLE_FLAG = 'N'
--AND AID.LINE_TYPE_LOOKUP_CODE <> 'TAX' 
--AND AID.FINAL_MATCH_FLAG is null
--AND AID.DISTRIBUTION_LINE_NUMBER = '2'
order by AI.INVOICE_NUM,PHA.segment1,RVHV.RECEIPT_NUM,AID.DISTRIBUTION_LINE_NUMBER;

Read more

[E-Bussiness Suite] Query All Resource PSI - Project Costing

Deskripsi 
     Untuk mendapatkan semua item resource per-project dari PSI (Project Costing) beserta nilai dari commitment , actual , cost budget , Actual + Commitment berdasarkan task_number level 3.

Howto
SELECT distinct PPAH.project_id
      ,PPAH.task_id
      ,PT.WBS_LEVEL
      ,PT.top_task_id
      --,PRLM.parent_member_id
      ,PTK.parent_member_id
      ,PT.task_name
      ,PT.TASK_NUMBER
      ,PRLM.alias
      ,PRLM.expenditure_type as Resource_Name
      ,NVL(PPAB.BASE_RAW_COST_ITD,0) as ITD_Cost_Budget
      ,NVL(PPAA.RAW_COST_ITD,0) as ITD_Act_Cost
      ,NVL(PPAC.CMT_RAW_COST_ITD,0) as Commitment_ITD 
      ,(NVL(PPAA.RAW_COST_ITD,0) + NVL(PPAC.CMT_RAW_COST_ITD,0)) as Total_Act_Com
      ,PPAB.BASE_RAW_COST_TOT 
      ,PPAB.ORIG_BURDENED_COST_TOT 
      ,(NVL(PPAB.BASE_RAW_COST_TOT,0) - NVL(PPAA.RAW_COST_ITD,0) + NVL(PPAC.CMT_RAW_COST_ITD,0) ) as estimate_to_complete
      
FROM pa_project_accum_headers PPAH
    ,pa_resource_list_members PRLM
    ,pa_project_accum_actuals PPAA
    ,PA_PROJECT_ACCUM_BUDGETS PPAB
    ,PA_PROJECT_ACCUM_COMMITMENTS PPAC
    ,pa_tasks PT
    ,(select distinct prlm.parent_member_id, pt.task_name
      from pa_resource_list_members PRLM
          ,pa_project_accum_headers PPAH
          ,pa_tasks PT
    
     where PT.project_id = :Project_ID
     and wbs_level = '2'
     and PT.PROJECT_ID = PPAH.PROJECT_ID
     AND PT.TASK_ID = PPAH.TASK_ID  
     and PPAH.resource_id=PRLM.resource_id
     order by task_name) PTK
    
where PT.project_id = :Project_ID
and wbs_level = '3'
and PT.PROJECT_ID = PPAH.PROJECT_ID
AND PT.TASK_ID = PPAH.TASK_ID  
and PPAH.resource_id=PRLM.resource_id
and PPAH.PROJECT_ACCUM_ID=PPAA.PROJECT_ACCUM_ID(+)
and PPAH.PROJECT_ACCUM_ID=PPAB.PROJECT_ACCUM_ID(+)
and PPAH.PROJECT_ACCUM_ID=PPAC.PROJECT_ACCUM_ID(+)
and PRLM.parent_member_id = ptk.parent_member_id(+)
--to see minus budget you add this line below .....
--and (NVL(PPAB.BASE_RAW_COST_TOT,0) - NVL(PPAA.RAW_COST_ITD,0) + NVL(PPAC.CMT_RAW_COST_ITD,0) ) < 0
order by pt.task_number 
;

Read more

[E-Bussiness Suite] Query Resources PSI - Project Costing

Deskripsi
     Untuk mendapatkan item resource dari PSI (Project Costing) beserta nilai dari commitment , actual , cost budget, Actual + Commitment berdasarkan task_number level 3.

Howto
SELECT PPAH.project_id
      ,PPAH.task_id
      ,PT.WBS_LEVEL
      ,PT.top_task_id
      --,PRLM.parent_member_id
      ,PT.task_name
      ,PT.TASK_NUMBER
      ,PRLM.alias
      ,PRLM.expenditure_type as Resource_Name
      ,NVL(PPAB.BASE_RAW_COST_ITD,0) as ITD_Cost_Budget
      ,NVL(PPAA.RAW_COST_ITD,0) as ITD_Act_Cost
      ,NVL(PPAC.CMT_RAW_COST_ITD,0) as Commitment_ITD
      ,(NVL(PPAA.RAW_COST_ITD,0) + NVL(PPAC.CMT_RAW_COST_ITD,0)) as Total_Act_Com
      ,PPAB.BASE_RAW_COST_TOT
      ,PPAB.ORIG_BURDENED_COST_TOT
   
FROM pa_project_accum_headers PPAH
    ,pa_resource_list_members PRLM
    ,pa_project_accum_actuals PPAA
    ,PA_PROJECT_ACCUM_BUDGETS PPAB
    ,PA_PROJECT_ACCUM_COMMITMENTS PPAC
    ,pa_tasks PT
 
where PT.project_id = :Project_ID_NUMBER
and PT.PROJECT_ID = PPAH.PROJECT_ID
AND PT.TASK_ID = PPAH.TASK_ID
and PPAH.resource_id=PRLM.resource_id
and PPAH.PROJECT_ACCUM_ID=PPAA.PROJECT_ACCUM_ID(+)
and PPAH.PROJECT_ACCUM_ID=PPAB.PROJECT_ACCUM_ID(+)
and PPAH.PROJECT_ACCUM_ID=PPAC.PROJECT_ACCUM_ID(+)
and PT.task_number = :TASK_NUMBER_LEVEL3 'for example 24.0001.01
order by pt.task_number
;

Read more

[E-Bussiness Suite] Limit Attachment File Size

Deskripsi
     Untuk membatasi attachment yang diupload secara global, baik melalui attachment purchasing requisition maupun pada purchasing order pada ERP - EBS.

Howto
1. Goto Profiler > System > Find > 'Upload File Size Limit'
2. Add Value in KB (KiloByte)
3. Save

Jika Upload misalkan dibatasi max 2MB dan kemudian diupload files dengan size diatas 2MB maka akan seharusnya akan muncul 'Exception' / gagal. jika tidak ada exception dan files berhasil tembus kemungkinan besar terdapat bugs, untuk pertolongan lebih lanjut contact metalink support atau support vendor oracle anda.

Read more

[E-Bussiness Suite] Query list all allocation storage database

Deskripsi
      DBA_SEGMENTS describes the storage allocated for all segments in the database.

How to
list all segment :
select *
from dba_segments

list segment table only :
select *
from dba_segments
where segment_type='TABLE'

list segment table for attachment : 
select *
from dba_segments
where segment_type='TABLE'
and segment_name = 'FND_LOBS'

Read more

[E-Bussiness Suite] Query get all attachment file size in oracle db

Deskripsi
    FND_LOBS table is one of the largest objects in an E-Business Suite instance. FND_LOBS table contains all the attachments which were attached by users in all the modules of EBS. the attachment go into fnd_lobs table as large objects. Not stored in Unix box or other.

Howto
get data file size every attachment :
select file_id
        ,file_name
        ,upload_date
        ,dbms_lob.getlength(file_data) size_In_byte
        ,file_content_type
        ,file_format
        ,language
        ,oracle_charset
from fnd_lobs;

get total size all attachment
select count(dbms_lob.getlength(file_data))
from fnd_lobs;

*tested, works

Read more

[E-Bussiness Suite] Query PR - PO - RR - INV_NUM - VCHR_PAYMENT

Deskripsi
    Query dibawah ini dapat melisting data mulai dari PR sampai Payment, MANTAP !!!! :D, untuk field"nya tinggal menyesuaikan mana yang ingin ditampilkan.

Howto
1. Activate show hidden view (we use invoice and payments view), you can see the instruction HERE
2. Running this query
----------------------------------------------------------------------------
select distinct        PRHA.segment1 as PR
                            , PHA.segment1 as PO
              ,RVHV.RECEIPT_NUM as RECEIPT_NUMBER
                    ,AI.INVOICE_NUM as INVOICE
,AC.DOC_SEQUENCE_VALUE as VOUCHER_PAYMENT
         
from po_requisition_headers_all PRHA
       ,po_requisition_lines_all PRLA
       ,po_line_locations_all PLLA
       ,po_headers_all PHA
       ,po_lines_all PLA
       ,po_distributions PD
       ,RCV_SHIPMENT_LINES RSL
       ,RCV_VRC_HDS_V RVHV
       ,ap_invoice_distributions AID
       ,AP_INVOICES AI
       ,AP_INVOICE_PAYMENTS_V AIP
       ,AP_CHECKS AC
     
where PRHA.segment1= :PR_NUMBER
and prha.requisition_header_id = PRLA.requisition_header_id(+)
and PRLA.line_location_id = PLLA.line_location_id(+)
and PHA.po_header_id(+) = PLA.po_header_id
and PLA.po_line_id(+) = plla.po_line_id
AND PHA.po_header_id = RSL.po_header_id (+)
AND RSL.shipment_header_id = RVHV.shipment_header_id(+)
AND PD.po_header_id(+) = PHA.po_header_id
AND AID.po_distribution_id(+) = PD.po_distribution_id
AND AI.INVOICE_ID(+) = AID.INVOICE_ID
AND AI.INVOICE_ID = AIP.INVOICE_ID(+)
AND AIP.CHECK_ID = AC.CHECK_ID(+);

Read more