APPS.OZF_GL_INTERFACE_PVT SQL Statements

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

Line: 22

select org_id
from   ozf_claims_all
where  claim_id = p_id;

Line: 27

select org_id
from   ozf_funds_utilized_all_b
where  utilization_id = p_id;

Line: 129

select u.amount
,      u.acctd_amount
,      u.currency_code
,      u.utilization_id
,      u.fund_id
,      u.component_type
,      u.component_id
,      u.object_type
,      u.object_id
,      u.product_level_type
,      u.product_id
,      u.price_adjustment_id
,      u.cust_account_id
from   ozf_funds_utilized_all_b u
where  u.utilization_id = p_id;

Line: 148

select u.amount
,      u.utilized_acctd_amount
,      u.currency_code
,      u.utilization_id
,      u.claim_line_util_id
,      f.fund_id
,      f.component_type
,      f.component_id
,      f.object_type
,      f.object_id
,      f.product_level_type
,      f.product_id
,      f.price_adjustment_id
,      f.cust_account_id
from   ozf_claim_lines_util_all u
,      ozf_claim_lines_all l
,      ozf_claims_all c
,      ozf_funds_utilized_all_b f
where  u.claim_line_id = l.claim_line_id
and    u.utilization_id = f.utilization_id
and    l.claim_id = c.claim_id
and    c.claim_id = p_id;

Line: 177

SELECT SUM(u.amount)
,      SUM(u.acctd_amount)
,      c.currency_code
FROM   ozf_claim_lines_util_all u
,      ozf_claim_lines_all l
,      ozf_claims_all c
WHERE  l.claim_id = c.claim_id
AND    l.earnings_associated_flag = 'T'
AND    l.claim_line_id = u.claim_line_id
-- [END OF 11.5.9 BUG 4021967 Fixing]
AND    c.claim_id = p_id
GROUP BY c.currency_code;

Line: 193

select u.amount
,      u.utilized_acctd_amount
,      u.currency_code
,      u.utilization_id
,      f.fund_id
,      f.component_type
,      f.component_id
,      f.object_type
,      f.object_id
,      f.product_level_type
,      f.product_id
,      f.price_adjustment_id
,      f.cust_account_id
,      c.claim_id
from   ozf_claim_lines_util_all u
,      ozf_claim_lines_all l
,      ozf_claims_all c
,      ozf_funds_utilized_all_b f
where  u.claim_line_id = l.claim_line_id
and    u.utilization_id = f.utilization_id
and    c.claim_id = l.claim_id
and    l.claim_line_id = p_id;

Line: 219

SELECT l.claim_currency_amount
,      l.acctd_amount
,      c.currency_code
,      c.claim_id
FROM   ozf_claim_lines_all l
,      ozf_claims_all c
WHERE  l.claim_id = c.claim_id
--AND    l.earnings_associated_flag = 'T'
AND    l.claim_line_id = p_id;

Line: 232

select u.amount
,      u.utilized_acctd_amount
,      u.currency_code
,      u.utilization_id
,      f.fund_id
,      f.component_type
,      f.component_id
,      f.object_type
,      f.object_id
,      l.item_type
,      l.item_id
,      f.price_adjustment_id
,      f.cust_account_id
,      c.claim_id
from   ozf_claim_lines_util_all u
,      ozf_claim_lines_all l
,      ozf_claims_all c
,      ozf_funds_utilized_all_b f
where  u.claim_line_id = l.claim_line_id
and    u.utilization_id = f.utilization_id
and    c.claim_id = l.claim_id
and    u.claim_line_util_id = p_id;

Line: 257

select osp.gl_id_ded_adj            -- expense account
,      osp.gl_id_accr_promo_liab    -- accrual liability account
,      osp.gl_id_ded_clearing       -- vendor clearing account
,      osp.gl_rec_clearing_account  -- receivables clearing account
FROM   ozf_sys_parameters_all osp
,      ozf_funds_utilized_all_b ofa
WHERE  osp.org_id = NVL(ofa.org_id, -99)
AND    ofa.utilization_id = p_id;

Line: 267

select osp.gl_id_ded_adj            -- expense account
,      osp.gl_id_accr_promo_liab    -- accrual liability account
,      osp.gl_id_ded_clearing       -- vendor clearing account
,      osp.gl_rec_clearing_account  -- receivables clearing account
FROM   ozf_sys_parameters_all osp
,      ozf_claims_all oc
WHERE  osp.org_id = NVL(oc.org_id, -99)
AND    oc.claim_id = p_id;

Line: 278

select ct.gl_id_ded_clearing       -- vendor clearing account
,      ct.gl_id_ded_adj_clearing   -- receivables clearing account
from   ozf_claim_types_all_b ct
,      ozf_claims_all c
where  ct.claim_type_id = c.claim_type_id
and    c.claim_id = cv_claim_id;

Line: 287

select claim_id
from   ozf_claim_lines_all
where  claim_line_id = cv_claim_line_id;

Line: 295

select code_combination_id
from   ozf_ae_lines_all
where  source_table = cv_source_table
and    source_id    = cv_source_id
and    ae_line_type_code = cv_account_type;

Line: 306

select cgl.code_combination_id
from   ozf_funds_utilized_all_b fu
,      oe_price_adjustments pa
,      oe_order_lines_all ol
,      oe_order_headers_all oh
,      ra_customer_trx_lines_all ctl
,      ra_cust_trx_line_gl_dist_all cgl
where fu.price_adjustment_id = pa.price_adjustment_id
and   ol.line_id = pa.line_id
and   ol.header_id = oh.header_id
and   ctl.sales_order = to_char(oh.order_number)
and   ctl.sales_order_line = ol.line_number
and   cgl.customer_trx_line_id = ctl.customer_trx_line_id
and   cgl.account_class = 'REV'
and   fu.utilization_id = p_utiz_id
and   fu.order_line_id = ctl.interface_line_attribute6; --fix for bug 7431334

Line: 327

select cgl.code_combination_id
from   ozf_funds_utilized_all_b fu
,      oe_price_adjustments pa
,      oe_order_lines_all ol
,      oe_order_headers_all oh
,      ra_customer_trx_lines_all ctl
,      ra_cust_trx_line_gl_dist_all cgl
where fu.price_adjustment_id = pa.price_adjustment_id
and   ol.line_id = pa.line_id
and   ol.header_id = oh.header_id
and   ctl.sales_order = to_char(oh.order_number)
and   ctl.sales_order_line = ol.line_number
and   ctl.INTERFACE_LINE_ATTRIBUTE11 = TO_CHAR(pa.price_adjustment_id)
and   cgl.customer_trx_line_id = ctl.customer_trx_line_id
and   cgl.account_class = 'REV'
and   fu.utilization_id = p_utiz_id;

Line: 348

select oh.order_number
,      ol.line_number
from   ozf_funds_utilized_all_b fu
,      oe_price_adjustments pa
,      oe_order_lines_all ol
,      oe_order_headers_all oh
where fu.price_adjustment_id = pa.price_adjustment_id
and   ol.line_id = pa.line_id
and   ol.header_id = oh.header_id
and   fu.utilization_id = p_utiz_id;

Line: 361

select c.payment_method
from   ozf_claims_all c
where  c.claim_id = cv_claim_id;

Line: 369

select parameter_value from oe_sys_parameters_all
where parameter_code = 'OE_DISCOUNT_DETAILS_ON_INVOICE'
and org_id = p_org_id;

Line: 860

SELECT f.fund_type
,      f.ledger_id
,      g.period_set_name
FROM   ozf_funds_all_b f
,      gl_sets_of_books g
,      ozf_funds_utilized_all_b u
WHERE  f.ledger_id = g.set_of_books_id
AND    f.fund_id = u.fund_id
AND    u.utilization_id = p_id;

Line: 871

SELECT c.claim_class
,      c.set_of_books_id
,      g.period_set_name
FROM   ozf_claims_all c
,      gl_sets_of_books g
WHERE  c.set_of_books_id = g.set_of_books_id
AND    c.claim_id = p_id;

Line: 1006

   SELECT tax_for
   FROM ozf_claim_sttlmnt_methods_all csm,ozf_claims_all c
   WHERE csm.settlement_method = c.payment_method
   AND c.claim_id =p_claim_id
   AND csm.org_id = c.org_id;

Line: 1172

select o.offer_code
from   ozf_funds_utilized_all_b fu
,      ozf_offers o
where  fu.plan_type = 'OFFR'
and    fu.plan_id = o.qp_list_header_id
and    fu.utilization_id = cv_utilization_id;

Line: 1180

select f.fund_number
from   ozf_funds_utilized_all_b fu
,      ozf_funds_all_b f
where  fu.fund_id = f.fund_id
and    fu.utilization_id = cv_utilization_id;

Line: 1187

select claim_number
from   ozf_claims_all
where  claim_id = cv_claim_id;

Line: 1302

select lu.claim_line_util_id
from   ozf_claim_lines_all ln
,      ozf_claim_lines_util_all lu
,      ozf_funds_utilized_all_b fu
,      ozf_offers o
,      ams_custom_setup_attr c
where  ln.claim_line_id = lu.claim_line_id
and    fu.utilization_id = lu.utilization_id
and    ln.item_type = 'PRODUCT'
and    ln.item_id is not null
and    fu.product_level_type = 'FAMILY'
and    fu.plan_type = 'OFFR'
and    fu.plan_id = o.qp_list_header_id
and    o.custom_setup_id = c.custom_setup_id
and    c.object_attribute = 'RVGL'
and    c.attr_available_flag = 'Y'
and    ln.claim_id = p_claim_id;

Line: 1792

SELECT G.period_name
FROM gl_period_statuses G
WHERE G.application_id = 101
AND G.closing_status in ('O', 'F')
AND NVL(G.adjustment_period_flag, 'N') = 'N'
AND G.set_of_books_id = p_sob_id
AND trunc(p_acc_date) between G.start_date and G.end_date;

Line: 1832

select gl_date
,      claim_date
,      org_id
from   ozf_claims_all
where  claim_id = p_id;

Line: 1839

select gl_date
from   ozf_funds_utilized_all_b
where  utilization_id = p_id;

Line: 1844

SELECT gl_date_type
FROM ozf_sys_parameters_all
WHERE org_id = NVL(p_id, -99);

Line: 1903

select org_id
from   ozf_claims_all
where  claim_id = p_id;

Line: 1908

select org_id
from   ozf_funds_utilized_all_b
where  utilization_id = p_id;

Line: 1997

select  meaning
from    fnd_lookup_types_tl
where   lookup_type = p_type;

Line: 2081

select code_combination_id_gain
from   ar_system_parameters_all
where org_id = p_org_id;

Line: 2086

select code_combination_id_loss
from   ar_system_parameters_all
where org_id = p_org_id;

Line: 2091

SELECT SUM(u.amount)
,      SUM(u.fxgl_acctd_amount)
,      c.currency_code
FROM   ozf_claim_lines_util_all u
,      ozf_claim_lines_all l
,      ozf_claims_all c
WHERE  l.claim_id = c.claim_id
AND    l.earnings_associated_flag = 'T'
AND    l.claim_line_id = u.claim_line_id
AND    c.claim_id = p_claim_id
GROUP BY c.currency_code;

Line: 2480

SELECT osp.post_to_gl
,      osp.transfer_to_gl_in
,      osp.gl_acct_for_offinv_flag
FROM   ozf_sys_parameters_all osp
,      ozf_funds_utilized_all_b ofa
WHERE  osp.org_id = NVL(ofa.org_id, -99)
AND    ofa.utilization_id = p_id;

Line: 2489

SELECT osp.post_to_gl
,      osp.transfer_to_gl_in
FROM   ozf_sys_parameters_all osp
,      ozf_claims_all oc
WHERE  osp.org_id = NVL(oc.org_id, -99)
AND    oc.claim_id = p_id;

Line: 2498

SELECT count(claim_line_id)
FROM   ozf_claim_lines_all
where  claim_id = p_id
and    earnings_associated_flag = 'T';

Line: 2505

select nvl(sum(lu.amount), 0)
from ozf_claim_lines_util_all lu
where lu.claim_line_id in (select l.claim_line_id
                        from ozf_claim_lines_all l
                        where l.claim_id = p_id);

Line: 2515

   SELECT accounting_event_id
   FROM ozf_acctng_events_all
   WHERE source_id = cv_source_id
   AND source_table = cv_source_table;

Line: 2817

select event_status_code
from   ozf_acctng_events_all
where  accounting_event_id = p_id;

Line: 2930

   UPDATE ozf_acctng_events_all
   SET    event_status_code = 'ACCOUNTED'
   where  accounting_event_id = l_accounting_event_id;

Line: 3059

    SELECT accounting_event_id
         , event_number
         , event_status_code
         , event_type_code
         , source_id
         , source_table
         , org_id
         , accounting_date
         , object_version_number
    from ozf_acctng_events_all
    where source_table = 'OZF_CLAIMS_ALL'
    and source_id = p_source_id;

Line: 3074

   select ae_header_id
         , ae_category
         , cross_currency_flag
         , description
         , gl_reversal_flag
         , period_name
         , set_of_books_id
         , gl_transfer_flag
         , org_id
         , object_version_number
   from ozf_ae_headers_all
   where accounting_event_id = p_accounting_event_id;

Line: 3089

   select ae_line_id
          , object_version_number
   from ozf_ae_lines_all
   where ae_header_id = p_ae_header_id;

Line: 3096

   select accounted_cr
         , accounted_dr
         , ae_line_number
         , ae_line_type_code
         , code_combination_id
         , currency_code
         , description
         , source_id
         , source_table
         , org_id
   from ozf_ae_lines_all
   where ae_header_id = p_ae_header_id;

Line: 3111

SELECT closing_status
FROM   gl_period_statuses a
     , ozf_sys_parameters b
WHERE  application_id = 222
  AND  a.set_of_books_id = b.set_of_books_id
  AND  l_acct_date BETWEEN start_date AND end_date
  AND  NVL(adjustment_period_flag,'N') = 'N';

Line: 3122

SELECT MIN(start_date)
FROM   gl_period_statuses a
     , ozf_sys_parameters b
WHERE  a.application_id = 222
AND    a.set_of_books_id =  b.set_of_books_id
AND    nvl(a.adjustment_period_flag,'N') = 'N'
AND    a.closing_status IN ( 'O','F');

Line: 3341

      UPDATE ozf_ae_headers_all
         SET gl_reversal_flag = 'Y'
      WHERE ae_header_id = l_ae_header_id;

Line: 3347

         OZF_Utility_PVT.debug_message('Not transferred to GL, delete SLA entries');

Line: 3360

            OZF_AE_LINE_PVT.Delete_Ae_Line(
                  p_api_version_number    => 1.0,
                  p_init_msg_list         => FND_API.G_FALSE,
                  p_commit                => FND_API.G_FALSE,
                  p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
                  x_return_status         => x_return_status,
                  x_msg_count             => x_msg_count,
                  x_msg_data              => x_msg_data,
                  p_ae_line_id            => l_ae_line_id,
                  p_object_version_number => l_ael_object_version_num);

Line: 3380

         OZF_Utility_PVT.debug_message('Deleted lines');

Line: 3384

      OZF_AE_HEADER_PVT.Delete_Ae_Header(
            p_api_version_number    => 1.0,
            p_init_msg_list         => FND_API.G_FALSE,
            p_commit                => FND_API.G_FALSE,
            p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
            x_return_status         => x_return_status,
            x_msg_count             => x_msg_count,
            x_msg_data              => x_msg_data,
            p_ae_header_id          => l_ae_header_id,
            p_object_version_number => l_aeh_object_version_num);

Line: 3402

         OZF_Utility_PVT.debug_message('Deleted header');

Line: 3406

      OZF_ACCTNG_EVENTS_PVT.Delete_Acctng_Events(
            p_api_version_number    => 1.0,
            p_init_msg_list         => FND_API.G_FALSE,
            p_commit                => FND_API.G_FALSE,
            p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
            x_return_status         => x_return_status,
            x_msg_count             => x_msg_count,
            x_msg_data              => x_msg_data,
            p_accounting_event_id   => l_accounting_event_id,
            p_object_version_number => l_ae_object_version_num);

Line: 3424

         OZF_Utility_PVT.debug_message('Deleted header');