R12 :Query for Bank Book Report

SELECT xe.event_type_code,to_char(ac.check_number) check_number,to_char(ac.check_date,'DD-MON-RRRR') check_date,ai.doc_sequence_value voucher_number,aps.vendor_name,apss.vendor_site_code,
       NVL(xal.accounted_dr,0) accounted_dr,NVL(xal.accounted_cr,0) accounted_cr,xe.event_id,gjb.name batch_name,gjh.je_source,ai.description
  FROM gl_je_lines gjl,
       gl_code_combinations gcc,
       gl_import_references gir,
       xla_ae_lines xal,
       xla_ae_headers xah,
       xla_events xe,
       ap_invoice_distributions_all aid,
       ap_invoices_all ai,
       ap_invoice_payments_all aip,
       ap_checks_all ac,
       ap_suppliers aps,
       ap_supplier_sites_all apss,
       gl_je_batches gjb,
       gl_je_headers gjh
 WHERE gjl.code_combination_id = gcc.code_combination_id
   AND gcc.segment3 = :p_accno
   AND gjl.je_header_id = gir.je_header_id
   AND gjl.je_line_num = gir.je_line_num
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xe.event_id = xah.event_id
   AND xe.event_id = aid.accounting_event_id
   AND ai.invoice_id = aid.invoice_id
   AND aip.invoice_id = aid.invoice_id
   AND aip.check_id = ac.check_id
   AND aps.vendor_id = ai.vendor_id
   AND ai.vendor_site_id = apss.vendor_site_id
   AND aid.dist_code_combination_id = gcc.code_combination_id
   AND gir.je_batch_id = gjb.je_batch_id
   AND gir.je_header_id = gjh.je_header_id
   AND gjl.period_name = :p_period
--   AND ac.check_date BETWEEN :p_from_date and :p_to_date
UNION  ALL
SELECT xe.event_type_code,acr.receipt_number,to_char(acr.receipt_date,'DD-MON-RRRR') check_date,acr.doc_sequence_value voucher_number,hp.party_name,bill_hl.city,
       NVL(xal.accounted_dr,0) accounted_dr,NVL(xal.accounted_cr,0) accounted_cr,xe.event_id,gjb.name batch_name,gjh.je_source,acr.comments
  FROM gl_je_lines gjl,
       gl_code_combinations gcc,
       gl_import_references gir,
       xla_ae_lines xal,
       xla_ae_headers xah,
       xla_events xe,
       ar_cash_receipt_history_all acrh,
       ar_cash_receipts_all acr,
       hz_cust_site_uses_all bill_hcsu,
       hz_cust_acct_sites_all bill_hcas,
       hz_party_sites bill_hps,
       hz_locations bill_hl,
       hz_parties hp,
       gl_je_batches gjb,
       gl_je_headers gjh
 WHERE gjl.code_combination_id = gcc.code_combination_id
   AND gcc.segment3 = :p_accno
   AND gjl.je_header_id = gir.je_header_id
   AND gjl.je_line_num = gir.je_line_num
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xe.event_id = xah.event_id
   AND xe.event_id = acrh.event_id
   AND acrh.cash_receipt_id = acr.cash_receipt_id
   AND bill_hcsu.site_use_id(+) = acr.customer_site_use_id
   AND bill_hcsu.cust_acct_site_id = bill_hcas.cust_acct_site_id(+)
   AND bill_hcas.party_site_id = bill_hps.party_site_id(+)
   AND bill_hps.location_id = bill_hl.location_id(+)
   AND bill_hps.party_id = hp.party_id(+)
   AND gir.je_batch_id = gjb.je_batch_id
   AND gir.je_header_id = gjh.je_header_id
   AND gjl.period_name = :p_period  
UNION ALL
SELECT xe.event_type_code,to_char(ac.check_number),to_char(ac.check_date,'DD-MON-RRRR') check_date,ac.doc_sequence_value voucher_number,
       aps.vendor_name,
       apss.vendor_site_code,
       CASE WHEN aip.amount <0 THEN ROUND(ABS(aip.amount*NVL(aip.exchange_rate,1)),3) ELSE 0 END accounted_dr,
       CASE WHEN aip.amount >0 THEN ROUND((aip.amount*NVL(aip.exchange_rate,1)),3) ELSE 0 END accounted_cr,
       xe.event_id,gjb.name batch_name,gjh.je_source,
       (select description from ap_invoices_all where invoice_id = aip.invoice_id)
  FROM gl_je_lines gjl,
       gl_code_combinations gcc,
       gl_import_references gir,
       xla_ae_lines xal,
       xla_ae_headers xah,
       xla_events xe,
       ap_invoice_payments_all aip,
       ap_checks_all ac,
       ap_suppliers aps,
       ap_supplier_sites_all apss,
       gl_je_batches gjb,
       gl_je_headers gjh
 WHERE gjl.code_combination_id = gcc.code_combination_id
   AND gcc.segment3 = :p_accno
   AND gjl.je_header_id = gir.je_header_id
   AND gjl.je_line_num = gir.je_line_num
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xe.event_id = xah.event_id
   AND xe.event_id = aip.accounting_event_id
   AND ac.check_id = aip.check_id
   AND aps.vendor_id = ac.vendor_id
   AND ac.vendor_site_id = apss.vendor_site_id
   AND gir.je_batch_id = gjb.je_batch_id
   AND gir.je_header_id = gjh.je_header_id
   AND gjl.period_name = :p_period
UNION ALL
SELECT NULL,NULL,to_char(gjh.date_created,'DD-MON-RRRR'),gjh.doc_sequence_value,NULL,NULL,
       NVL(gjl.accounted_dr,0) accounted_dr,NVL(gjl.accounted_cr,0) accounted_cr,NULL,gjb.name batch_name,gjh.je_source,NULL
  FROM gl_je_lines gjl,
       gl_code_combinations gcc,
       gl_je_batches gjb,
       gl_je_headers gjh
WHERE gjl.code_combination_id = gcc.code_combination_id
   AND gjl.period_name = :p_period
   AND gcc.segment3 = :p_accno      
   AND gjl.je_header_id = gjh.je_header_id
   AND gjh.je_batch_id = gjb.je_batch_id
   AND gjh.je_source = 'Manual'
ORDER BY check_date

Oracle R12 Query to find self consumption of finished goods

SELECT   msi.segment1 itemcode,msi.description fg_descr, msi.primary_unit_of_measure fg_uom,
         ROUND(ABS((SELECT SUM (mmt1.transaction_quantity)
          FROM mtl_material_transactions mmt1
          WHERE trunc(mmt1.transaction_date) = :from_start_date
          AND mmt1.transaction_type_id = mtt.transaction_type_id
          AND mmt1.inventory_item_id = msi.inventory_item_id
          AND mmt1.organization_id = msi.organization_id)),3) prod_from_date,
          ROUND(ABS(SUM (mmt.transaction_quantity)),3) prod_date_range,
         ROUND((SELECT ABS(SUM (mmt1.transaction_quantity))
          FROM mtl_material_transactions mmt1
          WHERE 1=1
          AND mmt1.transaction_type_id = mtt.transaction_type_id
          AND mmt1.inventory_item_id = msi.inventory_item_id
          AND mmt1.organization_id = msi.organization_id
          AND mmt1.transaction_date BETWEEN
                   (SELECT start_date
                     FROM cm_cldr_dtl
                    WHERE calendar_code =
                             (SELECT calendar_code
                                FROM cm_cldr_dtl
                               WHERE :from_start_date BETWEEN start_date AND end_date
                               AND calendar_code = 'XX_CST_CL')
                      AND period_code = 1)
              AND (SELECT end_date
                     FROM cm_cldr_dtl
                    WHERE calendar_code =
                             (SELECT calendar_code
                                FROM cm_cldr_dtl
                               WHERE :from_start_date BETWEEN start_date AND end_date
                               AND calendar_code = 'XX_CST_CL')
                      AND period_code = 12)),3) prod_year
    FROM mtl_material_transactions mmt,
         mtl_transaction_types mtt,
         mtl_system_items_b msi,
         gme_batch_header gbh
   WHERE trunc(mmt.transaction_date) BETWEEN :from_start_date AND :to_start_date
     AND msi.organization_id = :p_org_id
     AND mmt.transaction_type_id = mtt.transaction_type_id
     AND mtt.transaction_type_name = 'WIP Issue'
     AND msi.inventory_item_id = NVL(:p_item_id,msi.inventory_item_id)
     AND mmt.inventory_item_id = msi.inventory_item_id
     AND mmt.organization_id = msi.organization_id
     AND mmt.transaction_source_id = gbh.batch_id
     AND msi.item_type = 'FG'
     AND EXISTS
     (SELECT 1
      FROM gme_material_details gmd,gme_batch_header gbh
      WHERE 1=1
      AND gmd.inventory_item_id = msi.inventory_item_id
      AND gmd.batch_id = gbh.batch_id
      AND gmd.organization_id = msi.organization_id
      AND line_type = -1
      AND msi.inventory_item_id in
         (SELECT gmd1.inventory_item_id
          FROM gme_material_details gmd1,gme_batch_header gbh1
          WHERE gbh1.batch_no = NVL(:P_BATCH_NO,gbh1.batch_no)
          AND gmd1.batch_id = gbh1.batch_id
          AND gmd1.line_type in (1,2)))
GROUP BY msi.segment1,msi.description, msi.primary_unit_of_measure,mtt.transaction_type_id,msi.inventory_item_id,msi.organization_id
ORDER BY msi.segment1

Oracle R12 Parameter mandatory based on another parameter

Objective:

                Generally we can enable to disable a parameter based on dependent value sets method. There may be scenario where we have to make a parameter mandatory based on another parameter value. For example, in Purchase Order Document Report, we have to make the Release Number parameter mandatory for Blanket PO and non-mandatory for Standard PO.

We need to create three parameters to achieve that. A parameter for PO Number, release number each and one parameter to make the release number mandatory or non-mandatory. In the following screenshot, PO Number, Release Number and Header Id are created for that respective purposes.

          

Value set for P_PO_NUM(PO Number) parameter

Value set for P_HEADER_ID parameter

             
         

The default type of parameter should be set as SQL Statement.

         

The default value query is given below.

select po_header_id from po_headers_all poh where poh.type_lookup_code = 'BLANKET' and segment1 = :$FLEX$.XX_PO_NUMBER

where XX_PO_NUMBER is the value set created for PO Number parameter.

Value set for P_RELEASE_NUM (Release Number) parameter

The release number parameter should be added with NVL clause in the query because it will be passed only for Blanket parameter but it should be made mandatory in Concurrent Program > Parameters.