Advanced Pricing Retrieve overview of modifiers.

Working with Oracle Advanced Pricing, keeps you alert en quickly lose overview. I have developed a query where you receive a list of modifiers which you can export to Excel for more analyse or easier reading.

select (select par.value  from v$parameter par
     where par.name = ‘mts_service’)||’, ‘||to_char(sysdate,’DD-MON-YYYY HH24:MI’)           Environment
,    decode(mh.list_type_code
    , ‘PRO’,’Promotion’
    ,
read more..

Retrieve Incomplete Invoices before month end

Below you find a script to retrieve all incomplete invoices for the month closing procedure. Just get the extra turnover in .

CREATE TABLE  XXTMP_EP_INCOMPLETE_INVOICES
AS
SELECT /*+RULE */
       hca.account_number                  klant_nr
,      ctx.trx_number                      factuur_nr
,      TO_CHAR(ctx.trx_date,’DD-MM-YYYY’)  factuur_datum
,      ctt.NAME                            factuur_type
,      ctx.customer_trx_id
,      ctx.bill_to_customer_id
,      ctx.cust_trx_type_id
FROM   apps.ra_cust_trx_types_all  ctt
,      apps.hz_cust_accounts     hca
,      apps.ra_customer_trx_all  ctx
WHERE  NVL(ctx.complete_flag,’N’) = ‘N’
AND    ctx.bill_to_customer_id    = hca.cust_account_id
AND    ctx.cust_trx_type_id       = ctt.cust_trx_type_id

Find duplicate bankaccounts in Oracle EBS

With the script below you can identify duplicatie bank accounts in your Oracle E-Business installation.

CREATE TABLE XXTMP_AH_DOUBLE_BANK_ACCOUNTS
AS
SELECT hca.account_number
,      hca.orig_system_reference
,      hca.cust_account_id
,      hpy.party_id
,      hpy.party_name
,      TRUNC(hpy.creation_date)  party_aangemaakt
,      bat.bank_account_num
,      COUNT(1)  atl_voorkomens
FROM   apps.ap_bank_accounts_all     bat
,      apps.ap_bank_account_uses_all bau
,      apps.hz_parties               hpy
,      apps.hz_cust_accounts         hca
WHERE  hca.orig_system_reference    LIKE ‘AH%’
AND    hca.party_id                 = hpy.party_id
AND    hca.cust_account_id          = bau.customer_id
AND    NVL(bau.end_date, SYSDATE+1) > SYSDATE
AND    bau.customer_site_use_id     IS NULL
AND    bau.external_bank_account_id = bat.bank_account_id
GROUP
read more..

Reverse Receipts / Receiptbatch

So now and then things go wrong . In our case we have created a receiptbatch and sent the file to the bank. The status of the cash receipt is SENT. But something is wrong and whole receipt batch or some receipts have to be reversed. In our case we have always batches with
read more..