Tuesday 4 October 2016

Query for PLA Register in India Localization

/* Formatted on 10/3/2016 8:29:01 PM (QP5 v5.114.809.3010) */
  SELECT   pl.organization_id,
           (SELECT   DISTINCT organization_name
              FROM   org_organization_definitions
             WHERE   organization_id = pl.organization_id)
              Org_name,
           (SELECT   DISTINCT organization_code
              FROM   org_organization_definitions
             WHERE   organization_id = pl.organization_id)
              Org_code,
           pl.register_id,
           pl.fin_year,
           pl.slno,
           pl.charge_account_id,
           pl.ref_document_id,
           TRUNC (pl.ref_document_date),
           NVL (pl.dr_invoice_date, pl.tr6_challan_date) Invoice_Date,
           NVL (pl.dr_invoice_no, pl.tr6_challan_no) Invoice_num,
           pl.location_id,
           TRUNC (pl.entry_date),
           pl.inventory_item_id,
           pl.vendor_cust_flag,
           pl.vendor_id,
           DECODE (
              pl.vendor_cust_flag,
              'C',
              (SELECT   party_name
                 FROM   hz_parties
                WHERE   party_id = (SELECT   party_id
                                      FROM   hz_cust_accounts
                                     WHERE   cust_account_id = pl.vendor_id)),
              'V',
              (SELECT   vendor_name
                 FROM   po_vendors
                WHERE   vendor_id = pl.vendor_id)
           )
              Customer_Vendor_name,
           DECODE (
              pl.vendor_cust_flag,
              'C',
              (SELECT   address3 || ' - ' || city
                 FROM   hz_locations
                WHERE   location_id =
                           (SELECT   location_id
                              FROM   hz_party_sites
                             WHERE   party_site_id =
                                        (SELECT   party_site_id
                                           FROM   hz_cust_acct_sites_all
                                          WHERE   cust_acct_site_id =
                                                     (SELECT   cust_acct_site_id
                                                        FROM   hz_cust_site_uses_all
                                                       WHERE   site_use_id =
                                                                  14474)))),
              'V',
              (SELECT   vendor_site_code || ' - ' || state
                 FROM   ap_supplier_sites_all
                WHERE   vendor_id = pl.vendor_id
                        AND vendor_site_id = pl.vendor_site_id)
           )
              Site_Name,
           pl.vendor_site_id,
           pl.excise_invoice_no,
           TRUNC (pl.transaction_date),
           pl.transaction_source_num,
           (pl.cr_basic_ed + pl.cr_other_ed) cr_Bed,
           pl.cr_additional_ed,
           (SELECT   SUM (credit)
              FROM   jai_cmn_rg_others jac
             WHERE   jac.source_register_id = pl.register_id
                     AND jac.tax_type IN
                              ('EXCISE_EDUCATION_CESS', 'CVD_EDUCATION_CESS'))
              cr_edu,
           (SELECT   SUM (credit)
              FROM   jai_cmn_rg_others jac
             WHERE   jac.source_register_id = pl.register_id
                     AND jac.tax_type IN
                              ('EXCISE_SH_EDU_CESS', 'CVD_SH_EDU_CESS'))
              cr_she_cess,
           (pl.dr_basic_ed + dr_other_ed) Dr_Bed,
           pl.dr_additional_ed,
           (SELECT   SUM (debit)
              FROM   jai_cmn_rg_others jac
             WHERE   jac.source_register_id = pl.register_id
                     AND jac.tax_type IN
                              ('EXCISE_EDUCATION_CESS', 'CVD_EDUCATION_CESS'))
              dr_edu,
           (SELECT   SUM (debit)
              FROM   jai_cmn_rg_others jac
             WHERE   jac.source_register_id = pl.register_id
                     AND jac.tax_type IN
                              ('EXCISE_SH_EDU_CESS', 'CVD_SH_EDU_CESS'))
              dr_she_cess,
           pl.other_tax_credit,
           pl.other_tax_debit,
           wda.delivery_id,
           wda.delivery_detail_id,
           wdd.item_description,
           (SELECT   concatenated_segments
              FROM   mtl_system_items_kfv
             WHERE   inventory_item_id = wdd.inventory_item_id
                     AND organization_id = pl.organization_id)
              item_code,
           wdd.shipped_quantity,
           wdd.unit_price,
           wdd.source_header_number,
           wdd.requested_quantity_uom,
           (SELECT   DISTINCT doc_sequence_value
              FROM   GL_JE_HEADERS
             WHERE   je_header_id IN
                           (SELECT   DISTINCT je_header_id
                              FROM   gl_je_lines
                             WHERE   reference_5 = TO_CHAR (wda.delivery_id)
                                     AND CODE_COMBINATION_ID =
                                           pl.CHARGE_ACCOUNT_ID))
              GL_DOC_SEQ,
           (SELECT   DISTINCT default_effective_date
              FROM   GL_JE_HEADERS
             WHERE   je_header_id IN
                           (SELECT   DISTINCT je_header_id
                              FROM   gl_je_lines
                             WHERE   reference_5 = TO_CHAR (wda.delivery_id)
                                     AND CODE_COMBINATION_ID =
                                           pl.CHARGE_ACCOUNT_ID))
              GL_Date
    FROM   jai_cmn_rg_pla_trxs pl,
           wsh_delivery_assignments wda,
           wsh_delivery_details wdd
   WHERE       pl.ref_document_id = wda.delivery_detail_id(+)
           AND wdd.delivery_detail_id(+) = wda.delivery_detail_id
           AND pl.organization_id = :organizaion_id
           AND pl.location_id = NVL (:location_id, pl.location_id)
           AND TRUNC (pl.transaction_date) BETWEEN TRUNC(NVL (
                                                            TO_DATE (
                                                               :p_from_date
                                                            ),
                                                            pl.transaction_date
                                                         ))
                                               AND  TRUNC(NVL (
                                                             TO_DATE (
                                                                :p_to_date
                                                             ),
                                                             pl.transaction_date
                                                          ))
ORDER BY   pl.slno

No comments:

Post a Comment