Query to find the Never Validated Status for AP Done in Sona

SELECT  invoice_num,
              invoice_date,
              invoice_type_lookup_code
FROM     ap_invoices_all
WHERE  org_id =246
AND         AP_INVOICES_UTILITY_PKG.get_approval_status
             (invoice_id,
              invoice_amount,
              payment_status_flag,
              invoice_type_lookup_code) = 'NEVER APPROVED'
AND       invoice_date  BETWEEN  '01-AUG-2011' AND '30-SEP-2011'

Query to find AR Receivables for the Category "CREDIT MEMOS" posted to GL Done in Sona

SELECT DISTINCT
               a.je_category,
               a.je_source,
               a.creation_date,
               a.running_total_cr,
               a.running_total_dr
FROM    gl_je_headers a,
               gl_je_batches_v b
WHERE  a.je_source='Receivables'
AND        a.status='P'
AND        a.je_category='Credit Memos'
AND        a.creation_date BETWEEN '1-AUG-11' AND '31-AUG-11'
AND        b.org_id=246
order by creation_date

Query to find the Type Lookup Code Status Done in Sona

1st Method
SELECT DISTINCT
             b.invoice_id,
             b.invoice_num,
             b.invoice_date,
             b.gl_date,
             b.invoice_currency_code,
             b.invoice_amount,
             b.invoice_type_lookup_code,
             a.period_name,
             a.dist_code_combination_id  
FROM   ap_invoice_distributions_all a,
            ap_invoices_all b
WHERE  a.invoice_id = b.invoice_id
AND       b.invoice_date BETWEEN '1-AUG-2011' AND '31-AUG-2011'
AND       b.org_id = 246

2nd Method
SELECT DISTINCT
             b.invoice_id,
             b.invoice_num,
             b.invoice_date,
             b.gl_date,
             b.invoice_currency_code,
             b.invoice_amount,
             b.invoice_type_lookup_code,
             a.period_name,
             a.dist_code_combination_id  
FROM   ap_invoice_distributions_all a,
            ap_invoices_all b
WHERE  a.invoice_id = b.invoice_id
AND       b.invoice_date BETWEEN '1-AUG-2011' AND '31-AUG-2011'
AND       b.invoice_type_lookup_code='DEBIT'
AND       b.org_id = 246

Query for the Invoice Number(AP) Which are not Transfer to GL Done in SONA

1st  Method:


SELECT  DISTINCT
              b.invoice_id,
              b.invoice_num,
              b.invoice_date,
              b.gl_date,
              b.invoice_currency_code,
              b.invoice_amount,
              b.invoice_type_lookup_code,
              a.period_name,
              a.dist_code_combination_id  
FROM    ap_invoice_distributions_all a,
               ap_invoices_all b
WHERE   a.invoice_id = b.invoice_id
AND         b.invoice_date BETWEEN  '1-AUG-2011' AND  '31-AUG-2011'
AND       a.posted_flag = 'N'
AND         b.org_id = 246

Pending Transaction Quantity Done in Sona

SELECT rsh.receipt_num,

            poh.segment1 po_num, 

            rr.routing_name,                     

              NVL (q1.receives, 0) "RECEIVED_QUANTITY",

           (NVL (q2.accepts, 0) + NVL (q3.rejects, 0)) "INSPECTED QTY",

               NVL (q2.accepts, 0) accepts, NVL (q3.rejects, 0) rejects,

               NVL (q4.deliver, 0) "DELIVERED_QUANTITY",

               (NVL (q1.receives, 0)-(NVL (q2.accepts, 0) + NVL (q3.rejects, 0)))

                                                                                                      "PENDING FOR INSPECTION",

               (NVL (q1.receives, 0) - NVL (q4.deliver, 0)) "PENDING FOR DELIVERY"

FROM    po_headers_all poh,

            rcv_shipment_lines rsl,

            rcv_shipment_headers rsh,

            rcv_transactions rt,

            rcv_routing_headers  rr,

           (SELECT transaction_type, shipment_header_id, SUM (quantity) receives

              FROM rcv_transactions

            WHERE transaction_type = 'RECEIVE'

            AND organization_id=247        

                GROUP BY transaction_type,shipment_header_id) q1,

             (SELECT transaction_type, shipment_header_id, SUM (quantity) accepts

             FROM rcv_transactions

           WHERE NVL (transaction_type, 0) = 'ACCEPT'

           AND organization_id=247

              GROUP BY transaction_type,shipment_header_id) q2,

            (SELECT transaction_type, shipment_header_id, NVL (SUM (quantity), 0) rejects

          FROM rcv_transactions

          WHERE NVL (transaction_type, 0) = 'REJECT'

             AND organization_id=247

          GROUP BY transaction_type,shipment_header_id) q3,

          (SELECT shipment_header_id, transaction_type,nvl(SUM(quantity),0) deliver

           FROM rcv_transactions

          WHERE nvl(transaction_type, 0) = 'DELIVER'

           AND organization_id=247

          GROUP BY transaction_type, shipment_header_id) q4         

WHERE 1=1

 AND  rsl.po_header_id(+) = poh.po_header_id

 AND  rsh.shipment_header_id = rsl.shipment_header_id

 AND  rsl.shipment_line_id = rt.shipment_line_id(+)

 AND  q1.shipment_header_id(+) = rt.shipment_header_id

 AND  q2.shipment_header_id(+) = rt.shipment_header_id

 AND  q3.shipment_header_id(+) = rt.shipment_header_id

 AND  q4.shipment_header_id(+) = rt.shipment_header_id

 AND  rr.routing_header_id=rt.routing_header_id

 AND  rr.routing_header_id in(1,2)

 AND (NVL (q1.receives, 0) - NVL (q4.deliver, 0)) > 0

 AND  rsh.receipt_num=1173

 AND  rsh.creation_date BETWEEN NVL(:FROM_RECEIPT_DATE,rsh.creation_date) 

                                    AND NVL(:TO_RECEIPT_DATE,rsh.creation_date)                   

 AND  poh.org_id=246

GROUP BY poh.segment1,

         rsh.receipt_num,

         rr.routing_name,

         q1.receives,

         q2.accepts,

         q3.rejects,

         q4.deliver

order by 1