DBA Data[Home] [Help]

APPS.JE_BE_CSSR_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 18

 SELECT substr(legal.tax_registration_number,3),
        legal.legal_entity_id
 FROM jg_zz_vat_rep_entities legal,
      jg_zz_vat_rep_entities acc
  WHERE acc.vat_reporting_entity_id = p_vat_reporting_entity_id
  AND  ((acc.entity_type_code = 'ACCOUNTING'
         AND acc.mapping_vat_rep_entity_id = legal.vat_reporting_entity_id)
        OR
         (acc.entity_type_code = 'LEGAL'
          and acc.vat_reporting_entity_id = legal.vat_reporting_entity_id)
         );
Line: 32

  select lower(ISO_LANGUAGE) from fnd_languages
  where language_code = userenv('LANG');
Line: 183

  select lower(ISO_LANGUAGE) from fnd_languages
  where language_code = userenv('LANG');
Line: 188

 select to_char(sysdate,'yyyy-mm-dd') ||'T'
        || to_char(sysdate,'hh:mi:ss')
 into l_sysdate
 from dual;
Line: 326

  select CHART_OF_ACCOUNTS_ID
    from gl_sets_of_books
  where set_of_books_id = p_sob;
Line: 332

  SELECT glp.period_year||'-'||decode(length(glp.period_num),2,to_char(glp.period_num),'0'||to_char(glp.period_num) ) period_num
        ,glp.start_date
        ,glp.end_date
   	    ,acc.ledger_id
	    ,acc.BALANCING_SEGMENT_VALUE
	    ,acc.entity_level_code
	    ,legal.legal_entity_id
  FROM gl_periods glp
      ,jg_zz_vat_rep_entities legal
      ,jg_zz_vat_rep_entities acc
  WHERE glp.period_set_name = legal.tax_calendar_name
  AND acc.vat_reporting_entity_id = p_vat_reporting_entity_id
  AND  ((acc.entity_type_code = 'ACCOUNTING'
       AND acc.mapping_vat_rep_entity_id = legal.vat_reporting_entity_id)
     OR
     (acc.entity_type_code = 'LEGAL'
       AND acc.vat_reporting_entity_id = legal.vat_reporting_entity_id)
      )
  AND period_name = l_rep_period;
Line: 442

	 'SELECT  sum(charges),
	        sum(revenue),
	        country,
	        currency,
	        rubic_code
	    FROM
	        (SELECT sum(nvl(ap_dist.amount,0)) charges,
                0 revenue,
                ven.country country,
                ap_inv.INVOICE_CURRENCY_CODE currency,
                ap_inv.invoice_id invoice_id,
                decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
   	    FROM
	        ap_invoices_all ap_inv,
                ap_supplier_sites_all ven ,
                ap_invoice_distributions_all ap_dist ,
                gl_code_combinations glcc,
                fnd_lookup_values lv
	    WHERE   ap_inv.vendor_site_id            = ven.vendor_site_id
            AND ap_dist.invoice_id               = ap_inv.invoice_id
            AND ap_dist.dist_code_combination_id = glcc.code_combination_id
            AND glcc.chart_of_accounts_id        = :v_coa_id1
            AND glcc.'||l_seg_name||'         = lv.lookup_code
            AND lv.lookup_type                   = ''JEBE_NBBN_CODES''
            AND lv.LANGUAGE                      = USERENV(''LANG'')
            AND ap_inv.set_of_books_id           = :v_sob_id1
            AND ven.country                     <> ''BE''
	    AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
	     	                                 ap_dist.ACCRUAL_POSTED_FLAG,
	     	                                 ap_dist.CASH_POSTED_FLAG,
 	                                         ap_dist.POSTED_FLAG, ap_inv.org_id) in (''Y'',''P'')
            AND ap_dist.accounting_date BETWEEN :v_start_date1 AND :v_end_date1
            GROUP BY ap_inv.INVOICE_CURRENCY_CODE,
                ven.country ,
                ap_inv.invoice_id,
                decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
                to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
            HAVING sum(nvl(ap_dist.base_amount,ap_dist.amount)) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
            UNION ALL
          SELECT  0 charges,
                sum(nvl(AMOUNT,0)) revenue,
                hzl.country country,
                invoice_currency_code currency,
                trx.customer_trx_id invoice_id,
                decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
           FROM
	        RA_CUST_TRX_LINE_GL_DIST_ALL gld ,
                ra_customer_trx_all trx ,
                HZ_CUST_ACCOUNTS hz_cust ,
                hz_parties parties ,
                hz_party_sites hz_ps ,
                hz_locations hzl ,
                hz_cust_site_uses_all hz_csu ,
                hz_cust_acct_sites_all hz_cas ,
                gl_code_combinations glcc,
                fnd_lookup_values lv
          WHERE
	    gld.customer_trx_id       = trx.customer_trx_id
            AND trx.BILL_TO_customer_ID   = HZ_CUST.cust_account_id
            AND hz_cust.party_id          = parties.party_id
            AND hz_cas.cust_account_id    = HZ_CUST.cust_account_id
            AND trx.BILL_TO_SITE_USE_ID   = HZ_CSU.SITE_USE_ID
            AND hz_cas.cust_acct_site_id  = hz_csu.cust_acct_site_id
            AND hz_ps.party_site_id       = hz_cas.party_site_id
            AND hz_ps.party_id            = parties.party_id
            AND hz_ps.location_id         = hzl.location_id
            AND gld.code_combination_id   = glcc.code_combination_id
            AND glcc.chart_of_accounts_id = :v_coa_id2
            AND lv.lookup_type            = ''JEBE_NBBN_CODES''
            AND glcc.'||l_seg_name||'     = lv.lookup_code
            AND lv.LANGUAGE               = USERENV(''LANG'')
            AND trx.set_of_books_id       = :v_sob_id2
            AND hzl.country              <> ''BE''
            AND gld.GL_DATE BETWEEN :v_start_date2 AND :v_end_date2
	    AND trx.complete_flag = ''Y''
 	    AND gld.posting_control_id <> -3
          GROUP BY hzl.COUNTRY,
                invoice_currency_code,
                trx.customer_trx_id,
                decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
                to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
          HAVING sum(acctd_AMOUNT) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
           )
         GROUP BY country, currency, rubic_code ';
Line: 530

	 'SELECT  sum(charges),
	        sum(revenue),
	        country,
	        currency,
	        rubic_code
	    FROM
	        (SELECT sum(nvl(ap_dist.amount,0)) charges,
                0 revenue,
                ven.country country,
                ap_inv.INVOICE_CURRENCY_CODE currency,
                ap_inv.invoice_id invoice_id,
                decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
   	    FROM
	        ap_invoices_all ap_inv,
                ap_supplier_sites_all ven ,
                ap_invoice_distributions_all ap_dist ,
                gl_code_combinations glcc,
                fnd_lookup_values lv
	    WHERE   ap_inv.vendor_site_id        = ven.vendor_site_id
            AND ap_dist.invoice_id               = ap_inv.invoice_id
            AND ap_dist.dist_code_combination_id = glcc.code_combination_id
            AND glcc.chart_of_accounts_id        = :v_coa_id1
            AND glcc.'||l_seg_name||'            = lv.lookup_code
            AND lv.lookup_type                   = ''JEBE_NBBN_CODES''
            AND lv.LANGUAGE                      = USERENV(''LANG'')
            AND ap_inv.set_of_books_id           = :v_sob_id1
    	    AND JE_BE_CSSR_PKG.get_bsv(ap_inv.set_of_books_id,glcc.chart_of_accounts_id,ap_dist.dist_code_combination_id) = :bsv1
            AND ven.country                     <> ''BE''
            AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
	                                         ap_dist.ACCRUAL_POSTED_FLAG,
	                                         ap_dist.CASH_POSTED_FLAG,
                                                 ap_dist.POSTED_FLAG, ap_inv.org_id) in (''Y'',''P'')
            AND ap_dist.accounting_date BETWEEN :v_start_date1 AND :v_end_date1
            GROUP BY ap_inv.INVOICE_CURRENCY_CODE,
                ven.country ,
                ap_inv.invoice_id,
                decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
                to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
            HAVING sum(nvl(ap_dist.base_amount,ap_dist.amount)) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
            UNION ALL
          SELECT  0 charges,
                sum(nvl(AMOUNT,0)) revenue,
                hzl.country country,
                invoice_currency_code currency,
                trx.customer_trx_id invoice_id,
                decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
           FROM
	        RA_CUST_TRX_LINE_GL_DIST_ALL gld ,
                ra_customer_trx_all trx ,
                HZ_CUST_ACCOUNTS hz_cust ,
                hz_parties parties ,
                hz_party_sites hz_ps ,
                hz_locations hzl ,
                hz_cust_site_uses_all hz_csu ,
                hz_cust_acct_sites_all hz_cas ,
                gl_code_combinations glcc,
                fnd_lookup_values lv
          WHERE
	    gld.customer_trx_id       = trx.customer_trx_id
            AND trx.BILL_TO_customer_ID   = HZ_CUST.cust_account_id
            AND hz_cust.party_id          = parties.party_id
            AND hz_cas.cust_account_id    = HZ_CUST.cust_account_id
            AND trx.BILL_TO_SITE_USE_ID   = HZ_CSU.SITE_USE_ID
            AND hz_cas.cust_acct_site_id  = hz_csu.cust_acct_site_id
            AND hz_ps.party_site_id       = hz_cas.party_site_id
            AND hz_ps.party_id            = parties.party_id
            AND hz_ps.location_id         = hzl.location_id
            AND gld.code_combination_id   = glcc.code_combination_id
            AND glcc.chart_of_accounts_id = :v_coa_id2
            AND lv.lookup_type            = ''JEBE_NBBN_CODES''
            AND glcc.'||l_seg_name||'     = lv.lookup_code
            AND lv.LANGUAGE               = USERENV(''LANG'')
            AND trx.set_of_books_id       = :v_sob_id2
	    AND JE_BE_CSSR_PKG.get_bsv(trx.set_of_books_id,glcc.chart_of_accounts_id,gld.code_combination_id) = :bsv2
            AND hzl.country              <> ''BE''
            AND gld.GL_DATE BETWEEN :v_start_date2 AND :v_end_date2
            AND trx.complete_flag = ''Y''
	    AND gld.posting_control_id <> -3
          GROUP BY hzl.COUNTRY,
                invoice_currency_code,
                trx.customer_trx_id,
                decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
                to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
          HAVING sum(acctd_AMOUNT) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
           )
         GROUP BY country, currency, rubic_code ';
Line: 621

	'SELECT  sum(charges),
        sum(revenue),
        country,
        currency,
        rubic_code
    FROM
        (SELECT sum(nvl(ap_dist.amount,0)) charges,
                0 revenue,
                ven.country country,
                ap_inv.INVOICE_CURRENCY_CODE currency,
                ap_inv.invoice_id invoice_id,
                decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
        FROM    ap_invoices_all ap_inv,
                ap_supplier_sites_all ven ,
                ap_invoice_distributions_all ap_dist ,
                fnd_lookup_values lv,
                gl_ledgers glr
        WHERE   ap_inv.legal_entity_id           = :p_legal_entity_id1
            AND ap_inv.vendor_site_id            = ven.vendor_site_id
            AND ap_dist.invoice_id               = ap_inv.invoice_id
            AND glr.ledger_id                    = ap_inv.set_of_books_id
            AND JE_BE_CSSR_PKG.get_accounting_segment(glr.chart_of_accounts_id,ap_dist.dist_code_combination_id)   = lv.lookup_code
            AND lv.lookup_type                   = ''JEBE_NBBN_CODES''
            AND lv.LANGUAGE                      = USERENV(''LANG'')
            AND ven.country                     <> ''BE''
            AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
	                                         ap_dist.ACCRUAL_POSTED_FLAG,
	                                         ap_dist.CASH_POSTED_FLAG,
                                                 ap_dist.POSTED_FLAG, ap_inv.org_id) in (''Y'',''P'')
            AND ap_dist.accounting_date BETWEEN :v_start_date1 AND :v_end_date1
        GROUP BY ap_inv.INVOICE_CURRENCY_CODE,
                ven.country ,
                ap_inv.invoice_id,
                decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
                to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
        HAVING sum(nvl(ap_dist.base_amount,ap_dist.amount)) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
        UNION ALL
        SELECT  0 charges,
                sum(nvl(AMOUNT,0)) revenue,
                hzl.country country,
                invoice_currency_code currency,
                trx.customer_trx_id invoice_id,
                decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)) rubic_code
        FROM    ra_cust_trx_line_gl_dist_all gld ,
                ra_customer_trx_all  trx ,
                hz_cust_accounts     hz_cust,
                hz_cust_site_uses_all   hz_csu,
                hz_cust_acct_sites_all  hz_cas,
                hz_parties           parties,
                hz_party_sites       hz_ps,
                hz_locations         hzl,
                fnd_lookup_values lv,
                gl_ledgers glr
        WHERE   trx.legal_entity_id       = :p_legal_entity_id2
	    AND gld.customer_trx_id       = trx.customer_trx_id
            AND trx.bill_to_customer_id   = hz_cust.cust_account_id
            AND hz_cust.party_id          = parties.party_id
            AND hz_cas.cust_account_id    = hz_cust.cust_account_id
            AND trx.bill_to_site_use_id   = hz_csu.site_use_id
            AND hz_cas.cust_acct_site_id  = hz_csu.cust_acct_site_id
            AND hz_ps.party_site_id       = hz_cas.party_site_id
            AND hz_ps.party_id            = parties.party_id
            AND hz_ps.location_id         = hzl.location_id
            AND trx.set_of_books_id       = glr.ledger_id
            AND lv.lookup_type            = ''JEBE_NBBN_CODES''
            AND lv.LANGUAGE               = USERENV(''LANG'')
            AND hzl.country              <> ''BE''
            AND gld.GL_DATE BETWEEN :v_start_date2 AND :v_end_date2
            AND trx.complete_flag = ''Y''
	    AND gld.posting_control_id <> -3
            AND JE_BE_CSSR_PKG.get_accounting_segment(glr.chart_of_accounts_id, gld.code_combination_id ) = lv.lookup_code
        GROUP BY hzl.COUNTRY,
                invoice_currency_code,
                trx.customer_trx_id,
                decode (instr(lv.description,'':''), 0,lv.description, substr(lv.description,0,instr(lv.description,'':'')-1)),
                to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
        HAVING sum(acctd_AMOUNT) >= to_number(nvl(lv.tag,-999999999999999999999999999999999999999))
        )
    GROUP BY country, currency, rubic_code ';
Line: 835

  SELECT application_column_name
  INTO   l_segment
  FROM   fnd_segment_attribute_values
  WHERE    id_flex_code               = 'GL#'
    AND    attribute_value            = 'Y'
    AND    segment_attribute_type     = 'GL_BALANCING'
    AND    application_id             = 101
    AND    id_flex_num = p_choac_id;
Line: 844

  EXECUTE IMMEDIATE 'SELECT '||l_segment ||
                  ' FROM gl_code_combinations '||
                  ' WHERE code_combination_id = '||p_cc_id
  INTO bal_segment_value;
Line: 881

   SELECT application_column_name
    INTO l_segment
   FROM FND_SEGMENT_ATTRIBUTE_VALUES
   WHERE id_flex_num            = p_coa_id --50714
   AND segment_attribute_type = 'GL_ACCOUNT'
   AND id_flex_code           = 'GL#'
   AND attribute_value        = 'Y'
   AND application_id         = 101;
Line: 892

   EXECUTE IMMEDIATE 'SELECT '||l_segment ||
                  ' FROM gl_code_combinations '||
                  ' WHERE code_combination_id = '||p_cc_id||
                  ' AND chart_of_accounts_id = '||p_coa_id
  INTO l_segment_value;