Просто красивый запрос... БОЛЬШОЙ...

viktor954

Да не убьют меня Модераторы, да не влепят мне "2", но ЧЕРТОВСКИ ПОНРАВИЛСЯ ЗАПРОС:


SELECT /*+ RULE */

       A.hdoc_id,

       hc.customer,

       hc.doc_number as hdoc_number,

       A.description,

       A.camount,

       A.sistem

 FROM    

  (SELECT doc_id,

                         doc_number,

                         modifier,

                         customer_id,

                         acpdba.pck_refs.contr_name( customer_id , acpdba.glbs.get_current_date, 5 ) as customer

   FROM acpdba.heat_contracts

        WHERE  :add > 0 

               AND  modifier IS NULL

                         AND  DECODE( :al_hregion_id , -1, -1, REGION_ID  ) = :al_hregion_id

                         AND  DECODE ( :al_hbranch_id, -1, -1, branch_id ) = :al_hbranch_id

                         AND  :customer_name IS NULL) OR (lower (ACPDBA.PCK_REFS.CONTR_NAME( CUSTOMER_ID , acpdba.pck_globals.get_current_date, 5  like '%' || lower (:customer_name) || '%' 

                    AND (:group = -1 OR 

                    (class_id in ( select class_id from acpdba.contr_classes_groups

                                                        where acpdba.contr_classes_groups.group_id in 

                                                                (SELECT acpdba.contr_groups.group_id

                                                                 FROM acpdba.contr_groups  

                                                                 START WITH acpdba.contr_groups.group_id = :group 

                                                                 CONNECT BY PRIOR "GROUP_ID" = "MASTER_ID"

          UNION 

        SELECT doc_id,

                         doc_number,

                         modifier,

                         customer_id,

                         acpdba.pck_refs.contr_name( customer_id , acpdba.glbs.get_current_date, 5 ) as customer

   FROM acpdba.heat_contracts

   WHERE modifier is null   

             AND doc_id IN ( :hdoc_id ) 

  ) hc,

 (

 SELECT  

        acpdba.invoices.contract_id as hdoc_id,

        acpdba.pck_docs.doc_description ( acpdba.payment_receipts_view.doc_id ) as description,

        SUM ( DECODE ( acpdba.invoices.invoice_type, 'И', 1, 'В', -1, 0 ) * acpdba.payment_closings.amount ) as camount,

        decode(acpdba.payment_receipts_view.opr_rep_id, 10113, 'Теплосбыт', 10111, 'Авизо' , 10112, 'Ошибочные суммы', '-1111' ) as sistem 

  FROM

       acpdba.invoices     ,

       acpdba.payment_closings,

       acpdba.payment_receipts_view,

                 acpdba.heat_operations  

  WHERE

         acpdba.payment_receipts_view.doc_date  between  :adt_beg_date and :adt_end_date

     AND acpdba.payment_receipts_view.doc_type = 'В'

     AND acpdba.payment_receipts_view.doc_id = acpdba.payment_closings.cls_id 

     AND acpdba.payment_closings.confirm_date between  :adt_beg_date and :adt_end_date

          AND acpdba.invoices.doc_id  = acpdba.payment_closings.doc_id

     AND acpdba.invoices.opr_id = acpdba.heat_operations.opr_id

 GROUP BY

        acpdba.invoices.contract_id,

        acpdba.invoices.invoice_type,

 /*        hc.doc_number,*/

        acpdba.payment_receipts_view.opr_rep_id,

 /*        hc.customer_id,*/

        acpdba.payment_receipts_view.doc_id 

 UNION ALL 

 SELECT   acpdba.invoices.contract_id as hdoc_id,

         acpdba.docs.doc_description ( acpdba.payment_receipts_view.doc_id ) as description,

         DECODE ( acpdba.invoices.invoice_type, 'В', -1 * acpdba.invoices.amount, acpdba.invoices.amount - acpdba.chrg.get_acfd_pre ( acpdba.invoices.doc_id, :adt_end_date, 'N' ) ) as camount ,

         DECODE ( acpdba.payment_receipts_view.opr_rep_id, 10113, 'Теплосбыт', 10111, 'Авизо' , 10112, 'Ошибочные суммы', '-1111' ) as sistem 

 FROM

       acpdba.invoices         ,

       acpdba.doc_dependencies ,

       acpdba.payment_receipts_view 

  WHERE

         ( acpdba.invoices.invoice_type = 'В' OR

           acpdba.invoices.amount != acpdba.chrg.get_acfd_pre ( acpdba.invoices.doc_id, :adt_end_date, 'N' ) )

     AND acpdba.payment_receipts_view.doc_type = 'В'

     AND acpdba.doc_dependencies.parent_id = acpdba.payment_receipts_view.doc_id

     AND acpdba.invoices.doc_id = acpdba.doc_dependencies.child_id

     AND acpdba.invoices.doc_date between :adt_beg_date and :adt_end_date

     AND acpdba.invoices.opr_id = TO_NUMBER ( acpdba.glbs.get_environment ( 'АВАНСОВЫЙ ПЛАТЕЖ' ) )

 UNION ALL 

 SELECT  

        acpdba.invoices.contract_id as hdoc_id,

        acpdba.pck_docs.doc_description ( acpdba.cash_desk_receipts.doc_id ) as description,

        SUM( DECODE ( acpdba.invoices.invoice_type, 'И', 1, 'В', -1, 0 ) * acpdba.payment_closings.amount ) as camount,

        DECODE(acpdba.cash_desk_receipts.opr_id, 105, 'Через кассу', ' для кассовых ошибка') as sistem 

  FROM

       acpdba.invoices     ,

       acpdba.documents , 

       acpdba.payment_closings,

       acpdba.heat_operations   ,

       acpdba.cash_desk_receipts

  WHERE

         ( acpdba.invoices.invoice_type = 'И' OR TRUNC ( acpdba.documents.fldr_id, -2 ) / 100 = 139 )

     AND acpdba.payment_closings.cls_id = acpdba.documents.doc_id

     AND acpdba.invoices.doc_id  = acpdba.payment_closings.doc_id

     AND acpdba.cash_desk_receipts.doc_id = acpdba.payment_closings.cls_id

     AND acpdba.cash_desk_receipts.doc_date  between  :adt_beg_date and :adt_end_date

     AND acpdba.payment_closings.confirm_date between  :adt_beg_date and :adt_end_date

     AND acpdba.cash_desk_receipts.order_type = 'П'

     AND acpdba.invoices.opr_id = acpdba.heat_operations.opr_id

 GROUP BY

        acpdba.invoices.contract_id,

        acpdba.invoices.invoice_type,

 /*        acpdba.heat_contracts.doc_number,*/

        acpdba.cash_desk_receipts.opr_id,

 /*        acpdba.heat_contracts.customer_id, */

        acpdba.cash_desk_receipts.doc_id 

UNION ALL 

SELECT  acpdba.invoices.contract_id as hdoc_id,

        acpdba.pck_docs.doc_description ( acpdba.cash_desk_receipts.doc_id ) as description,

        DECODE ( acpdba.invoices.invoice_type, 'В', -1 * acpdba.invoices.amount, acpdba.invoices.amount - acpdba.chrg.get_acfd_pre ( acpdba.invoices.doc_id, :adt_end_date, 'N' ) ) as camount ,

        DECODE ( acpdba.cash_desk_receipts.opr_id, 105, 'Через кассу', ' для кассовых ошибка') as sistem  

 FROM

       acpdba.invoices     ,

       acpdba.doc_dependencies     ,

       acpdba.cash_desk_receipts 

  WHERE 

         ( acpdba.invoices.invoice_type = 'В' OR

           acpdba.invoices.amount != acpdba.chrg.get_acfd_pre ( acpdba.invoices.doc_id, :adt_end_date, 'N' ) )

     AND acpdba.cash_desk_receipts.order_type = 'П'

     AND acpdba.doc_dependencies.parent_id = acpdba.cash_desk_receipts.doc_id

     AND acpdba.invoices.doc_id = acpdba.doc_dependencies.child_id

     AND acpdba.invoices.doc_date between :adt_beg_date and :adt_end_date

     AND acpdba.invoices.opr_id = TO_NUMBER ( acpdba.glbs.get_environment ( 'АВАНСОВЫЙ ПЛАТЕЖ' ) )

UNION ALL 

SELECT  

        acpdba.invoices.contract_id as hdoc_id,

        acpdba.pck_docs.doc_description (

a10063

Это где такое?

yolki

очевидно, в каком-то банке, если судить по именам некоторых параметров

durka82

Какой же он красивый?!
Он просто большой
Оставить комментарий
Имя или ник:
Комментарий: