[Database][SQL Server] Log file filesize too big

Deskripsi
     i not recommend use shrink but if it urgent you can use it because it effect reduce perfomance to database.

How to 
1. run query , to get physical name of files data or log.
select name from dbname.sys.database_files where type=1

2. to use shrink use this 

Backup log dbname with truncate_only

go

DBCC shrinkfile('logicalfilename',100)

but it still not recommend use shrink , you must give a limitation of the autogrowth file data / log !!

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] 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 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

[E-Bussiness Suite] Query PO Amount list by Month - Year

Deskripsi
   Query untuk melisting PO beserta dengan Total Amount.

Howto
1. Karena kita menggunakan view yang recordnya di hidden oleh oracle maka untuk melihatnya gunakan cara INI 
2. Jalankan Query dibawah 

select PRHA.segment1 as PO_NUMBER
        ,PDV.GL_ENCUMBERED_DATE
        ,PO_AMOUNT.AMOUNT

from PO_HEADERS_ALL PRHA
       ,PO_DISTRIBUTIONS_V PDV
       ,PO_LINES_ALL PLA
       ,(select  PRHA.po_header_id
                  ,SUM(PLA.UNIT_PRICE*PLA.QUANTITY) as Amount

         from PO_HEADERS_ALL PRHA
                ,PO_LINES_ALL PLA

              where PRHA.authorization_status = 'APPROVED'
               AND PRHA.po_header_id = PLA.po_header_id
             
              group by PRHA.po_header_id) PO_AMOUNT

where PRHA.authorization_status = 'APPROVED'
AND PRHA.po_header_id = PDV.po_header_id
AND PRHA.po_header_id = PLA.po_header_id
AND PRHA.po_header_id = PO_AMOUNT.po_header_id
-- Low Date Month
AND to_number(to_char(PDV.GL_ENCUMBERED_DATE,'mm')) >= '1'
-- High Date Month
AND to_number(to_char(PDV.GL_ENCUMBERED_DATE,'mm')) <= '12'
-- Year Date
AND to_char(PDV.GL_ENCUMBERED_DATE,'yyyy') = '2011'

group by PRHA.segment1
        ,PO_AMOUNT.AMOUNT
        ,PDV.GL_ENCUMBERED_DATE
order by PRHA.segment1

3. jika masih bermasalah tidak menampilkan record dan menggunakan software Toad coba baca INI

4. jika hasil query ga cocok tolong infokan lewat comment ntar ane cek lagi :D, terima kasih.

Read more

[E-Bussiness Suite] Query PO - RR - Voucher(INV) - Voucher(PAYMENT)

Deskripsi
   Query untuk melisting PO beserta dengan RECEIPT NUMBER , VOUCHER(INVOICE) , VOUCHER(PAYMENT).

Howto
1. Karena kita menggunakan view yang recordnya di hidden oleh oracle maka untuk melihatnya gunakan cara INI
2. Jalankan Query dibawah

select  distinct PRHA.segment1 as PO_NUMBER
       ,RVHV.RECEIPT_NUM as RECEIPT_NUMBER
       ,AI.DOC_SEQUENCE_VALUE as VOUCHER_INVOICE
       ,AC.DOC_SEQUENCE_VALUE AS VOUCHER_PAYMENT
 
from PO_HEADERS_ALL PRHA
    ,RCV_SHIPMENT_LINES RSL
    ,RCV_VRC_HDS_V RVHV
    ,AP_INVOICES AI
    ,AP_INVOICE_PAYMENTS_V AIP
    ,AP_CHECKS AC
 
where PRHA.segment1 = '129752'
AND PRHA.po_header_id = RSL.po_header_id
AND RSL.shipment_header_id = RVHV.shipment_header_id
AND AI.invoice_id IN (SELECT apid.invoice_id FROM ap_invoice_distributions apid, po_distributions pod WHERE apid.po_distribution_id = pod.po_distribution_id AND pod.po_header_id = prha.po_header_id AND (pod.po_release_id = '' OR '' is null) AND (pod.po_line_id = '' OR '' is null) AND (pod.line_location_id = '' OR '' is null) AND (pod.po_distribution_id = '' OR '' is null))
AND AI.INVOICE_ID = AIP.INVOICE_ID
AND AIP.CHECK_ID = AC.CHECK_ID;

3. jika masih bermasalah tidak menampilkan record dan menggunakan software Toad coba baca INI

4. jika hasil query ga cocok tolong infokan lewat comment ntar ane cek lagi :D, terima kasih.


--Update Query,
   1. Add Description of PO
   2. Filter by Date Range, Month and Year


select  distinct prha.po_header_id
       ,PRHA.segment1 as PO_NUMBER
       ,PRHA.comments as Description
       ,RVHV.RECEIPT_NUM as RECEIPT_NUMBER
       ,AI.INVOICE_NUM as NUMBER_INVOICE
       ,AI.DOC_SEQUENCE_VALUE as VOUCHER_INVOICE
       ,AC.DOC_SEQUENCE_VALUE AS VOUCHER_PAYMENT
       ,to_char(PDV.GL_ENCUMBERED_DATE,'dd/mm/yyyy') as GL_DATE
     
from PO_HEADERS_ALL PRHA
    ,PO_DISTRIBUTIONS_V PDV
    ,RCV_SHIPMENT_LINES RSL
    ,RCV_VRC_HDS_V RVHV
    ,AP_INVOICES AI
    ,AP_INVOICE_PAYMENTS_V AIP
    ,AP_CHECKS AC
 
where
--PRHA.segment1 = '129752'
PRHA.po_header_id = PDV.po_header_id
AND PRHA.po_header_id = RSL.po_header_id
AND RSL.shipment_header_id = RVHV.shipment_header_id
AND AI.invoice_id IN (SELECT apid.invoice_id FROM ap_invoice_distributions apid, po_distributions pod WHERE apid.po_distribution_id = pod.po_distribution_id AND pod.po_header_id = prha.po_header_id AND (pod.po_release_id = '' OR '' is null) AND (pod.po_line_id = '' OR '' is null) AND (pod.line_location_id = '' OR '' is null) AND (pod.po_distribution_id = '' OR '' is null))
AND AI.INVOICE_ID = AIP.INVOICE_ID
AND AIP.CHECK_ID = AC.CHECK_ID
-- Low Date Month
AND to_number(to_char(PDV.GL_ENCUMBERED_DATE,'mm')) >= '1'
-- High Date Month
AND to_number(to_char(PDV.GL_ENCUMBERED_DATE,'mm')) <= '12'
-- Year Date
AND to_char(PDV.GL_ENCUMBERED_DATE,'yyyy') = '2010'

Read more

[E-Bussiness Suite] Query PO tanpa Receipt Number by Date

Deskripsi
      Query untuk melisting PO yang tidak memiliki RECEIPT NUMBER dan difilter berdasarkan tanggal.

Howto
select distinct PRHA.segment1 as PO_NUMBER

from PO_HEADERS_ALL PRHA
       ,RCV_SHIPMENT_LINES RSL
 
where PRHA.CREATION_DATE >= TO_DATE('17/05/2013','DD/MM/YY')
AND PRHA.CREATION_DATE <= TO_DATE('22/05/2013','DD/MM/YY')
AND PRHA.po_header_id = RSL.po_header_id(+)
AND RSL.po_header_id is null

order by PRHA.segment1;



-- Update--
Jika ingin ditambahkan dengan vendor dan buyer maka querynya seperti ini :

select distinct PRHA.segment1 AS PO_NUMBER
        ,PV.VENDOR_NAME AS VENDOR
        ,HE.FULL_NAME AS BUYER

from PO_HEADERS_ALL PRHA
    ,RCV_SHIPMENT_LINES RSL
    ,PO_VENDORS PV
    ,HR_EMPLOYEES HE
 
where PRHA.CREATION_DATE >= TO_DATE('17/05/2013','DD/MM/YY')
AND PRHA.CREATION_DATE <= TO_DATE('22/05/2013','DD/MM/YY')
AND PRHA.po_header_id = RSL.po_header_id(+)
AND PRHA.VENDOR_ID = PV.VENDOR_ID
AND PRHA.AGENT_ID = HE.EMPLOYEE_ID
AND RSL.po_header_id is null

order by PRHA.segment1;

Read more