CENVAT CLAIM - INDIA TAX PART

SELECT   ROWNUM, bb.receipt_num, bb.dat, bb.vendor_name,
         bb.excise_duty_reg_no, bb.vendor_type, bb.cenvat_amount, bb.se,
         bb.CV, bb.ae, bb.ec, bb.sc, bb.organization_id, bb.description,
         bb.unit_of_measure, bb.item_code, bb.excise_invoice_no,
         bb.excise_invoice_date, bb.quantity_shipped, bb.inventory_item_id,
         CASE
            WHEN bb.vendor_type = 'Manufacturer'
               THEN bb.cenvat_amount
            WHEN bb.vendor_type IS NULL
               THEN bb.cenvat_amount
            ELSE 0
         END mfg_amt_cen,
         CASE
            WHEN bb.vendor_type = 'First Stage Dealer'
               THEN bb.cenvat_amount
            WHEN bb.vendor_type = 'Second Stage Dealer'
               THEN bb.cenvat_amount
            ELSE 0
         END dea_amt_cen,
         CASE
            WHEN bb.vendor_type = 'Manufacturer'
               THEN bb.CV
            WHEN bb.vendor_type IS NULL
               THEN bb.CV
            ELSE 0
         END mfg_amt_cvd,
         CASE
            WHEN bb.vendor_type = 'First Stage Dealer'
               THEN bb.CV
            WHEN bb.vendor_type = 'Second Stage Dealer'
               THEN bb.CV
            ELSE 0
         END dea_amt_cvd,
         CASE
            WHEN bb.vendor_type = 'Manufacturer'
               THEN bb.ec
            WHEN bb.vendor_type IS NULL
               THEN bb.ec
            ELSE 0
         END mfg_amt_ec,
         CASE
            WHEN bb.vendor_type = 'First Stage Dealer'
               THEN bb.ec
            WHEN bb.vendor_type = 'Second Stage Dealer'
               THEN bb.ec
            ELSE 0
         END dea_amt_ec,
         CASE
            WHEN bb.vendor_type = 'Manufacturer'
               THEN bb.sc
            WHEN bb.vendor_type IS NULL
               THEN bb.sc
            ELSE 0
         END mfg_amt_sc,
         CASE
            WHEN bb.vendor_type = 'First Stage Dealer'
               THEN bb.sc
            WHEN bb.vendor_type = 'Second Stage Dealer'
               THEN bb.sc
            ELSE 0
         END dea_amt_sc,
         CASE
            WHEN bb.vendor_type = 'Manufacturer'
            AND bb.unit_of_measure = 'NOS'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type IS NULL AND bb.unit_of_measure = 'NOS'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'Each'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'SET'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'SET of 2'
               THEN bb.quantity_shipped
            ELSE 0
         END nos_m,
         CASE
            WHEN bb.unit_of_measure = 'KGM'
            AND bb.vendor_type = 'Manufacturer'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type IS NULL AND bb.unit_of_measure = 'KGM'
               THEN bb.quantity_shipped
            ELSE 0
         END kgs_m,
         CASE
            WHEN bb.unit_of_measure = 'PKT'
            AND bb.vendor_type = 'Manufacturer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'PKT' AND bb.vendor_type IS NULL
               THEN bb.quantity_shipped
            ELSE 0
         END pcs_m,
         CASE
            WHEN bb.unit_of_measure = 'MTR'
            AND bb.vendor_type = 'Manufacturer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'MTR' AND bb.vendor_type IS NULL
               THEN bb.quantity_shipped
            ELSE 0
         END mtr_m,
         CASE
            WHEN bb.unit_of_measure = 'Litre'
            AND bb.vendor_type = 'Manufacturer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'Litre' AND bb.vendor_type IS NULL
               THEN bb.quantity_shipped
            ELSE 0
         END ltr_m,
         CASE                                                  --DEALER
            WHEN bb.vendor_type = 'First Stage Dealer'
            AND bb.unit_of_measure = 'NOS'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type = 'First Stage Dealer'
            AND bb.unit_of_measure = 'Each'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type = 'First Stage Dealer'
            AND bb.unit_of_measure = 'SET'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type = 'First Stage Dealer'
            AND bb.unit_of_measure = 'SET of 2'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type = 'Second Stage Dealer'
            AND bb.unit_of_measure = 'NOS'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type = 'Second Stage Dealer'
            AND bb.unit_of_measure = 'Each'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type = 'Second Stage Dealer'
            AND bb.unit_of_measure = 'SET'
               THEN bb.quantity_shipped
            WHEN bb.vendor_type = 'Second Stage Dealer'
            AND bb.unit_of_measure = 'SET of 2'
               THEN bb.quantity_shipped
            ELSE 0
         END nos_d,
         CASE
            WHEN bb.unit_of_measure = 'KGM'
            AND bb.vendor_type = 'First Stage Dealer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'KGM'
            AND bb.vendor_type = 'Second Stage Dealer'
               THEN bb.quantity_shipped
            ELSE 0
         END kgs_d,
         CASE
            WHEN bb.unit_of_measure = 'PKT'
            AND bb.vendor_type = 'First Stage Dealer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'PKT'
            AND bb.vendor_type = 'Second Stage Dealer'
               THEN bb.quantity_shipped
            ELSE 0
         END pcs_d,
         CASE
            WHEN bb.unit_of_measure = 'MTR'
            AND bb.vendor_type = 'First Stage Dealer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'SHE'
            AND bb.vendor_type = 'First Stage Dealer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'MTR'
            AND bb.vendor_type = 'Second Stage Dealer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'SHE'
            AND bb.vendor_type = 'Second Stage Dealer'
               THEN bb.quantity_shipped
            ELSE 0
         END mtr_d,
         CASE
            WHEN bb.unit_of_measure = 'Litre'
            AND bb.vendor_type = 'First Stage Dealer'
               THEN bb.quantity_shipped
            WHEN bb.unit_of_measure = 'Litre'
            AND bb.vendor_type = 'Second Stage Dealer'
               THEN bb.quantity_shipped
            ELSE 0
         END ltr_d
    FROM (SELECT DISTINCT b.receipt_num, b.dat, b.vendor_name,
                          b.excise_duty_reg_no, b.vendor_type,
                          b.cenvat_amount, SUM (b.sed) se, SUM (b.cvd) CV,
                          SUM (b.aed) ae, SUM (b.edu_cess) ec,
                          SUM (b.she_cess) sc, b.organization_id,
                          b.description, b.unit_of_measure, b.item_code,
                          b.inventory_item_id, b.excise_invoice_no,
                          b.excise_invoice_date, b.quantity_shipped
                     FROM (SELECT DISTINCT rsh.receipt_num,
                                           jcr2a.transaction_date dat,
                                           ap.vendor_name,
                                           jcvs.excise_duty_reg_no,
                                           NVL (jcvs.vendor_type,
                                                NULL
                                               ) vendor_type,
                                           jrcc.cenvat_amount,
                                           rsl.quantity_shipped, NULL sed,
                                           (SELECT SUM (tax_amount)
                                              FROM jai_rcv_line_taxes
                                             WHERE shipment_header_id =
                                                      rsl.shipment_header_id
                                               AND tax_type LIKE 'CVD%') cvd,
                                           (SELECT SUM (tax_amount)
                                              FROM jai_rcv_line_taxes
                                             WHERE shipment_header_id =
                                                      rsl.shipment_header_id
                                               AND tax_type = 'ADDITIONAL_CVD')
                                                                          aed,
                                           (SELECT SUM (tax_amount)
                                              FROM jai_rcv_line_taxes
                                             WHERE shipment_header_id =
                                                        rsl.shipment_header_id
                                               AND tax_type =
                                                       'EXCISE_EDUCATION_CESS')
                                                                     edu_cess,
                                           (SELECT SUM (tax_amount)
                                              FROM jai_rcv_line_taxes
                                             WHERE shipment_header_id =
                                                        rsl.shipment_header_id
                                               AND tax_type =
                                                          'EXCISE_SH_EDU_CESS')
                                                                     she_cess,
                                           jrl.organization_id,
                                           msib.description,
                                           rsl.unit_of_measure,
                                           msib.segment1 item_code,
                                           jrl.excise_invoice_no,
                                           jrl.excise_invoice_date,
                                           msib.inventory_item_id
                                      FROM rcv_shipment_headers rsh,
                                           rcv_shipment_lines rsl,
                                           jai_rcv_lines jrl,
                                           jai_rcv_headers jrh,
                                           ap_suppliers ap,
                                           jai_cmn_vendor_sites jcvs,
                                           po_lines_all pol,
                                           jai_rcv_cenvat_claims jrcc,
                                           jai_rcv_line_taxes jrlt,
                                           mtl_system_items_b msib,
                                           jai_cmn_rg_23ac_ii_trxs jcr2a
                                     WHERE rsh.shipment_header_id =
                                                        rsl.shipment_header_id
                                       AND jcr2a.inventory_item_id =
                                                        msib.inventory_item_id
                                       AND TO_CHAR (jcr2a.transaction_date) =
                                                  TO_CHAR (jrcc.creation_date)
                                       AND jcr2a.organization_id =
                                                           jrl.organization_id
                                       AND jrl.excise_invoice_no =
                                                       jcr2a.excise_invoice_no
                                       AND jrl.claim_modvat_flag = 'Y'
                                       AND jrh.shipment_header_id =
                                                        rsh.shipment_header_id
                                       AND jrl.shipment_header_id =
                                                        jrh.shipment_header_id
                                       AND jrl.shipment_line_id =
                                                          rsl.shipment_line_id
                                       AND ap.vendor_id = rsh.vendor_id
                                       AND jcvs.vendor_id = ap.vendor_id
                                       AND jcvs.vendor_site_id = 0
                                       AND pol.po_header_id = rsl.po_header_id
                                       AND jrcc.shipment_line_id =
                                                          rsl.shipment_line_id
                                       AND jrlt.shipment_header_id =
                                                        rsh.shipment_header_id
                                       AND msib.inventory_item_id =
                                                                   rsl.item_id
                                       AND jrl.organization_id =
                                                          msib.organization_id
                                       AND jrl.organization_id =
                                              NVL (:p_org,
                                                   jrl.organization_id)
                                       AND TRUNC (jcr2a.transaction_date)
                                              BETWEEN NVL
                                                        (TO_DATE
                                                                (:p_start_date),
                                                         TRUNC
                                                            (jcr2a.transaction_date
                                                            )
                                                        )
                                                  AND NVL
                                                         (TO_DATE (:p_end_date),
                                                          TRUNC (SYSDATE)
                                                         )) b
                 GROUP BY b.receipt_num,
                          b.dat,
                          b.vendor_name,
                          b.excise_duty_reg_no,
                          b.vendor_type,
                          b.cenvat_amount,
                          b.sed,
                          b.cvd,
                          b.aed,
                          b.edu_cess,
                          b.she_cess,
                          b.organization_id,
                          b.description,
                          b.unit_of_measure,
                          b.item_code,                
                          b.excise_invoice_no,
                          b.excise_invoice_date,
                          b.quantity_shipped,
                          b.inventory_item_id
                 ORDER BY b.receipt_num) bb
ORDER BY ROWNUM

Sales Collection Analysis Report Query (Aging Style)

SELECT
sal_per_name,
sb0,
sb1,
sb2,
sb3,
sb4,
sb5,
sb6,
(sb0+sb1+sb2+sb3+sb4+sb5+sb6)g_t
from(
select
sal_per_name,
sum(b0) sb0,
sum(b1) sb1,
sum(b2) sb2,
sum(b3) sb3,
sum(b4) sb4,
sum(b5) sb5,
sum(b6) sb6
from
(select
sal_per_name,
case
when days_past_due between 0 and 30 then amt else 0 end b0,
case
when days_past_due between 31 and 60 then amt else 0 end b1,
case
when days_past_due between 61 and 90 then amt else 0 end b2,
case
when days_past_due between 91 and 120 then amt else 0 end b3,
case
when days_past_due between 121 and 180 then amt else 0 end b4,
case
when days_past_due between 181 and 365 then amt else 0 end b5,
case
when days_past_due between 366 and 9999 then amt else 0 end b6
from
(
select
sal_per_name ,
amt,
days_past_due
from
(
select distinct
nvl( jre.resource_name,arpv.attribute2) sal_per_name, arpv.receipt_number,
arpv.amount_applied amt,
case
when ceil(sysdate-ps.due_date)>0 then ceil(sysdate-ps.due_date) else 0  end days_past_due
from
hr_operating_units hou,
ar_cash_receipts_all acra,
ar_payment_schedules_all ps,
ar_receivable_applications_v arpv,
ra_customer_trx_all rct,
ra_cust_trx_line_salesreps_all rcsa,
ra_salesreps_all rsa,
jtf_rs_resource_extns_vl jre
where
acra.cash_receipt_id=arpv.cash_receipt_id
and arpv.trx_number=rct.trx_number
and rct.customer_trx_id=rcsa.customer_trx_id
and rcsa.salesrep_id=rsa.salesrep_id
and rsa.resource_id=jre.resource_id
and acra.status='APP'
 and ps.trx_number = arpv.trx_number
and hou.organization_id=acra.org_id
and hou.organization_id=nvl(:xx_org_id,hou.organization_id)
and trunc(acra.receipt_date) between nvl(:date_from,trunc(acra.receipt_date)) and nvl(:date_to,trunc(acra.receipt_date))
union
select distinct
nvl(jre.resource_name,acra.attribute1) sal_per_name ,acra.receipt_number,
 acra.applied_amount amt,
case
when ceil(to_date(sysdate)-ps.due_date)>0 then ceil(to_date(sysdate)-ps.due_date) else 0  end days_past_due
from
ar_cash_receipts_v acra,ar_payment_schedules_all ps,jtf_rs_resource_extns_vl jre,ra_salesreps_all rsa,ra_cust_trx_line_salesreps_all rcsa,
ra_customer_trx_all rct,ar_receivable_applications_v arpv
where
 acra.receipt_status='UNAPP'
 and acra.org_id = nvl(:xx_org_id,acra.org_id)
 and ps.trx_number = arpv.trx_number
and trunc(acra.receipt_date ) between nvl(:date_from,trunc(acra.receipt_date)) and nvl(:date_to,trunc(acra.receipt_date))
and acra.applied_amount <> 0
and rsa.resource_id=jre.resource_id
and rcsa.salesrep_id=rsa.salesrep_id
and rct.customer_trx_id=rcsa.customer_trx_id
and acra.cash_receipt_id=arpv.cash_receipt_id
and arpv.trx_number=rct.trx_number
))
)
group by sal_per_name)