P2P Cycle Query

SELECT pla.*
  FROM po_headers_all pha,
       po_lines_all pla,
       po_line_locations_all plla,
       po_distributions_all pda,
       rcv_shipment_lines rsl,
       rcv_shipment_headers rsh,
       ap_invoice_lines_all aila,
       ap_invoices_all aia,
       ap_invoice_distributions_all aida,
       ap_invoice_payments_all aip,
       ap_payment_schedules_all aps
 WHERE     pha.po_header_id = 130896
       AND pha.po_header_id = pla.po_header_id
       AND pla.po_line_id = plla.po_line_id
       AND plla.LINE_LOCATION_ID = pda.LINE_LOCATION_ID
       AND pha.po_header_id = rsl.po_header_id
       AND pla.po_line_id = rsl.po_line_id
       AND rsl.shipment_header_id = rsh.shipment_header_id
       AND pha.po_header_id = aila.po_header_id
       AND pla.po_line_id = aila.po_line_id
       AND aila.invoice_id = aia.invoice_id
       AND aila.invoice_id = aida.invoice_id
       AND aila.LINE_NUMBER = aida.Invoice_LINE_NUMBER
       AND aia.invoice_id = aip.invoice_id
       AND aip.invoice_id = aps.invoice_id
       AND aip.payment_num = aps.payment_num

Oaf Links

http://oracledot.blogspot.in/p/oaf-classes-uses.html

Function To Get GRN Quantity by Transaction Type wise

CREATE OR REPLACE FUNCTION APPS.grn_qty_new (
   p_shipment_header_id   NUMBER,
   p_shipment_line_id     NUMBER,
   p_transaction_type     VARCHAR2
)
   RETURN NUMBER
IS
   v_qty                NUMBER:=0;
   v_qty_f              NUMBER;
   v_transaction_type   VARCHAR2 (30);
   v_transaction_id     NUMBER;
   v_count number;
   v_routing_name varchar2(30);
   c_qty number:=0;
ret_rcv_qty number:=0;
v_correct_qty number:=0;
v_rtv_qty number:=0;

   CURSOR c1
   IS
      SELECT rt.*
        FROM rcv_shipment_headers rsh,
             rcv_shipment_lines rsl,
             rcv_transactions rt
       WHERE rsh.shipment_header_id = rsl.shipment_header_id
         AND rsl.shipment_header_id = rt.shipment_header_id
         AND rsl.shipment_line_id = rt.shipment_line_id
         AND rsh.shipment_header_id =
                         p_shipment_header_id
                                             --rsh.RECEIPT_NUM='5102140004389'
         AND rt.shipment_line_id = p_shipment_line_id                  --61178
         AND rt.transaction_type = p_transaction_type;
BEGIN
   v_qty_f := 0;

   FOR r1 IN c1
   LOOP 
  
  
       
                            begin
                              SELECT count(*)
                              into v_count
                              FROM rcv_transactions rt
                              WHERE rt.parent_transaction_id = r1.transaction_id;
                            exception
                              when no_data_found then
                               v_count:=0;                           
                            end;
                           
         if v_count>0then
      
         
          FOR i IN (SELECT rt.transaction_type, rt.transaction_id,rt.QUANTITY
                  FROM rcv_transactions rt
                 WHERE rt.parent_transaction_id = r1.transaction_id)
         
              LOOP
               
                   
                          
                           
                            if i.transaction_type='RETURN TO RECEIVING'then
                           
                           
                                
                                 ret_rcv_qty:=ret_rcv_qty+(-1*i.QUANTITY);
                           
                            elsif i.transaction_type='CORRECT' then
                           
                            v_correct_qty:=v_correct_qty+i.QUANTITY;
                            elsif i.transaction_type='RETURN TO VENDOR' then
                            v_rtv_qty:=v_rtv_qty+(-1*i.QUANTITY);
                            end if;
                 
                 
             
               
              end loop;
              v_qty:=nvl(ret_rcv_qty,0)+nvl(v_correct_qty,0)+nvl(v_rtv_qty,0)+nvl(r1.QUANTITY,0);
         elsif v_count=0 then
        
         v_qty:=r1.QUANTITY;
         
          end if;
             
       
   

      v_qty_f := v_qty;--+r1.quantity;
   END LOOP;                                              --cursor c1 loop end

   RETURN v_qty_f;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN (-1);
END;
/

India Localization Taxes and customer and supplier Tables

PO Taxes Table:-

jai_po_taxes                              /*table for puchase order taxes */ join is po_line_id

GRN Taxes Table:-
JAI_RCV_LINE_TAXES             /*table for GRN taxes */ join is shipment_line_id

Payable Invoice Invoice Tax Table:-

jai_ap_match_inv_taxes               /*table for Payable invoice  taxes */ 
JAI_AP_INVOICE_LINES         /* Table for payable invoice for non po related taxes*/

when we deduct TDS invoice against Supplier Invoice that time we create credit memo so to link between main invoice, tds credit memo and invoice of Tax authority to whom we pay that tax all this link is availbale in following table:-

jai_ap_tds_thhold_trxs                /* TDS tax invoice table */


Tax Creation Table and Tax Categories tables :-

jai_cmn_taxes_all                      /*table for taxes type deatiles */
JAI_CMN_TAX_CTGS_ALL         /* tax categories header table */
JAI_CMN_TAX_CTG_LINES     /* tax categories line table */



Supplier,customer and inventory org additional Information Tables:-

jai_cmn_vendor_sites                         /*table india lovalization vendor sites */
jai_cmn_inventory_orgs                    /*table india lovalization inventory organizations */
jai_cmn_cus_addresses                                   /*table for customer  information Details */
        
 Order Taxes Table:-     

JAI_OM_OE_SO_LINES                         /*India Localization Order lines table* /

JAI_OM_OE_SO_TAXES                           /* table for order taxes */


AR Invoice Taxes Tables:-

jai_ar_trxs                                                  /*table for AR invoice india localization header */
jai_ar_trx_lines                                    /*table for AR invoice india localization lines */
jai_ar_trx_tax_lines                             /*table for AR invoice taxes */

BOE Related Table:-


JAI_CMN_BOE_HDRS                                    Stores BOE header info when a BOE Invoice is created through IL
JAI_CMN_BOE_DTLS                                     Detail table for BOE Invoices

Regime Related Tables:-

JAI_RGM_DEFINITIONS                                Stores regime information.
JAI_RGM_TAXES                                           This table stores tax details for transactions having TCS tax type. 

RG23 Related tables:-
JAI_CMN_RG_23AC_I_TRXS             Stores Information of RG23A/C records and known as Quantity Register.
JAI_CMN_RG_23AC_II_TRXS       Stores Information of RG23A/C Part II Details. Also known as Amount Register
JAI_CMN_RG_23D_TRXS                       Quantity register for Trading Organizations
JAI_CMN_RG_BALANCES                 Store the current balances of RG23A, RG23C and PLA Registers 

PLA register Tables:-
JAI_CMN_RG_PLA_TRXS                Stores the Transaction Information of PLA Register.
JAI_CMN_RG_PLA_HDRS             Stores PLA header Infomation when a PLA invoice is created in AP module
JAI_CMN_RG_PLA_DTLS             Stores PLA Detail Information when a PLA Invoice is created in AP Module

AR To GL link through Subledger Links and Query

SELECT
         ACCOUNT_NUMBER,
         ACCOUNT_NAME,
         TRX.TRX_NUMBER INVOICE_NUMBER,
         GJB.NAME BATCH_NAME,
         GJH.NAME JOURNAL_NAME,
         GJH.RUNNING_TOTAL_ACCOUNTED_DR TOTAL_DEBIT_AMOUNT,
         GJH.RUNNING_TOTAL_ACCOUNTED_CR TOTAL_CREDIT_AMOUNT,
         GIR.REFERENCE_9 INVOICE_AMOUNT, ACCOUNTING_CLASS_CODE,
         gjl.ACCOUNTED_DR,gjl.ACCOUNTED_CR
   FROM    
        GL_JE_HEADERS  GJH,
        GL_JE_LINES         GJL,
        GL_JE_BATCHES   GJB,
        GL_IMPORT_REFERENCES GIR,
        XLA_AE_LINES         XAL,
        XLA_AE_HEADERS  XAH,
        XLA_DISTRIBUTION_LINKS LK,
        RA_CUST_TRX_LINE_GL_DIST_ALL GL_DIST,
        RA_CUSTOMER_TRX_ALL TRX,
        HZ_CUST_ACCOUNTS_ALL CUST_ACCT
WHERE
            GJH.JE_HEADER_ID   =    GJL.JE_HEADER_ID
  --AND GJH.JE_HEADER_ID = 952528
  AND   GJH.JE_BATCH_ID      =    GJB.JE_BATCH_ID
  AND   GJH.JE_HEADER_ID    =   GIR.JE_HEADER_ID
  AND   GJL.JE_LINE_NUM       =   GIR.JE_LINE_NUM
  AND GJB.JE_BATCH_ID         =   GIR.JE_BATCH_ID
  AND JE_SOURCE                  =   'Receivables'
 -- AND GJH.STATUS                  =  'P'
  AND GIR.GL_SL_LINK_ID        =   XAL.GL_SL_LINK_ID
  AND GIR.GL_SL_LINK_TABLE =  XAL.GL_SL_LINK_TABLE
  AND XAL.AE_HEADER_ID      =   XAH.AE_HEADER_ID
  AND LK.AE_HEADER_ID       =  XAL.AE_HEADER_ID
  AND LK.AE_LINE_NUM         =  XAL.AE_LINE_NUM
  AND LK.AE_HEADER_ID       =  XAH.AE_HEADER_ID
AND GL_DIST.CUST_TRX_LINE_GL_DIST_ID = LK.SOURCE_DISTRIBUTION_ID_NUM_1
  AND GL_DIST.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
  AND CUST_ACCT.CUST_ACCOUNT_ID = TRX.BILL_TO_CUSTOMER_ID
  AND GIR.REFERENCE_9 IS NOT NULL
  and ACCOUNT_NUMBER=1584
 
 -- AND LK.SOURCE_DISTRIBUTION_TYPE = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
 -- AND LK.APPLICATION_ID      =  222
 -- AND XAL.ACCOUNTING_CLASS_CODE IN ('RECEIVABLE','REVENUE')
 -- AND GL_DIST.ACCOUNT_SET_FLAG = 'N'
 -- AND TRX.COMPLETE_FLAG = 'Y'
  AND TRX_NUMBER = 'WVA/SLB/15/8362'
  AND GJH.JE_HEADER_ID = 952528
  --AND GJB.JE_BATCH_ID = 422909

Function To Get Financial Year Of Date

===== Function To Get Financial Year Of Date========


create or replace function finacial_year(p_date date) return varchar2 is
v_financial_year varchar2(10);
begin

SELECT
          CASE
             WHEN TO_CHAR (EXTRACT (MONTH FROM p_date))
                    BETWEEN 1
                        AND 3
                THEN TO_CHAR
                           (EXTRACT (YEAR FROM p_date) - 1
                           )
             WHEN TO_CHAR (EXTRACT (MONTH FROM p_date))
                    BETWEEN 4
                        AND 12
                THEN TO_CHAR (EXTRACT (YEAR FROM p_date))
          END
       || '-'
       || CASE
             WHEN TO_CHAR (EXTRACT (MONTH FROM p_date))
                    BETWEEN 1
                        AND 3
                THEN TO_CHAR
                           (EXTRACT (YEAR FROM p_date)
                           )
             WHEN TO_CHAR (EXTRACT (MONTH FROM p_date))
                    BETWEEN 4
                        AND 12
                THEN TO_CHAR (EXTRACT (YEAR FROM p_date)+1)
          END
          into
          v_financial_year
  FROM dual;
 
  return v_financial_year;
end function finacial_year;


======= Second Query===========


SELECT TO_CHAR (sysdate),
       DECODE (TO_CHAR (sysdate, 'mon'),
               'jan', TO_CHAR (sysdate, 'yyyy')
                -  1
                || '-'
                || TO_CHAR (sysdate, 'yyyy'),
               'feb', TO_CHAR (sysdate, 'yyyy')
                -  1
                || '-'
                || TO_CHAR (sysdate, 'yyyy'),
               'mar', TO_CHAR (sysdate, 'yyyy')
                -  1
                || '-'
                || TO_CHAR (sysdate, 'yyyy'),
               'apr', TO_CHAR (sysdate, 'yyyy')
                +  1
                || '-'
                || TO_CHAR (sysdate, 'yyyy'),
               'may', TO_CHAR (sysdate, 'yyyy')
                +  1
                || '-'
                || TO_CHAR (sysdate, 'yyyy'),
               'jun', TO_CHAR (sysdate, 'yyyy')
                || '-'
                || (TO_CHAR (sysdate, 'yyyy') + 1),
               'jul', TO_CHAR (sysdate, 'yyyy')
                || '-'
                || (TO_CHAR (sysdate, 'yyyy') + 1),
               'aug', TO_CHAR (sysdate, 'yyyy')
                || '-'
                || (TO_CHAR (sysdate, 'yyyy') + 1),
               'sep', TO_CHAR (sysdate, 'yyyy')
                || '-'
                || (TO_CHAR (sysdate, 'yyyy') + 1),
               'oct', TO_CHAR (sysdate, 'yyyy')
                || '-'
                || (TO_CHAR (sysdate, 'yyyy') + 1),
               'nov', TO_CHAR (sysdate, 'yyyy')
                || '-'
                || (TO_CHAR (sysdate, 'yyyy') + 1),
               'dec', TO_CHAR (sysdate, 'yyyy')
                || '-'
                || (TO_CHAR (sysdate, 'yyyy') + 1)
              )
  FROM dual