PO Flow

Stage 1: PO Creation
PO_HEADERS_ALL

select po_header_id
from po_headers_all
where segment1 =;

select *
from po_headers_all
where po_header_id =;
PO_LINES_ALL
select *
from po_lines_all
where po_header_id =;
PO_LINE_LOCATIONS_ALL

select *
from po_line_locations_all
where po_header_id =;

PO_DISTRIBUTIONS_ALL

select *
from po_distributions_all
where po_header_id =;

PO_RELEASES_ALL

SELECT *
FROM po_releases_all
WHERE po_header_id =;

Stage 2: Once PO is received, data is moved to respective receving tables and inventory tables

select *
from rcv_shipment_headers
where shipment_header_id in
(select shipment_header_id
from rcv_shipment_lineswhere po_header_id =);

RCV_SHIPMENT_LINES

select *
from rcv_shipment_lines
where po_header_id =;

RCV_TRANSACTIONS

select *
from rcv_transactions
where po_header_id =;

RCV_ACCOUNTING_EVENTS

SELECT *
FROM rcv_Accounting_Events
WHERE rcv_transaction_id IN
(select transaction_id
from rcv_transactionswhere po_header_id =);

RCV_RECEIVING_SUB_LEDGER

select
* from
rcv_receiving_sub_ledger
where rcv_transaction_id IN
(select transaction_id
from rcv_transactions
where po_header_id =);

RCV_SUB_LEDGER_DETAILS

select *
from rcv_sub_ledger_details
where rcv_transaction_id IN
(select transaction_id
from rcv_transactions
where po_header_id =);

MTL_MATERIAL_TRANSACTIONS
select *
from mtl_material_transactions
where transaction_source_id =;

MTL_TRANSACTION_ACCOUNTS
select *
from mtl_transaction_accounts
where transaction_id IN
(select transaction_id
from mtl_material_transactions
where transaction_source_id =);

Stage 3: Invoicing details

AP_INVOICE_DISTRIBUTIONS_ALL

select *
from ap_invoice_distributions_all
where po_distribution_id in
(select po_distribution_id
from po_distributions_all
where po_header_id =);

AP_INVOICES_ALL

select *
from ap_invoices_all
where invoice_id in
(select invoice_id
from ap_invoice_distributions_all
where po_distribution_id in
( select po_distribution_id
from po_distributions_all
where po_header_id =));

Stage 4 : Mostly there is tie up with Project related PO

PA_EXPENDITURE_ITEMS_ALL

select *
from pa_expenditure_items_all peia
where peia.orig_transaction_reference IN
(select to_char(transaction_id)
from mtl_material_transactions
where transaction_source_id = );

Stage 5 : General Ledger

GL_INTERFACE

select * from gl_interface gli
where user_je_source_name =’Purchasing’
and gl_sl_link_table =’RSL’
and reference21=’PO’
and exists (select 1
from rcv_receiving_sub_ledger rrsl
where gli.reference22 =RRSL.reference2
and GLI.reference23 =RRSL.reference3
AND GLI.reference24 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactionswhere po_header_id ));

GL_IMPORT_REFERENCES

SELECT
*FROM gl_import_references GLIR
WHERE reference_1=’PO’
AND gl_sl_link_table =’RSL’
AND EXISTS ( SELECT 1 FROM
rcv_receiving_sub_ledger RRSL
WHERE GLIR.reference_2 =RRSL.reference2
AND GLIR.reference_3 =RRSL.reference3
AND GLIR.reference_4 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id
from rcv_transactions
where po_header_id =))

BOM SQL

SELECT m.segment1 assembly_item,
b.assembly_item_id,
b.bill_sequence_id assembly_bom_seqid,
m.segment1 component_item,
c.component_item_id,
c.bill_sequence_id component_bom_seqid
FROM bom_bill_of_materials b,
mtl_system_items m,
bom_inventory_components c,
mtl_system_items m1
WHERE b.organization_id = :org_id
AND b.assembly_item_id = m.inventory_item_id
AND c.component_item_id = m1.inventory_item_id
AND m1.organization_id = m.organization_id
and c.bill_sequence_id = b.bill_sequence_id
AND m.planning_make_buy_code = 1
AND m.enabled_flag = 'Y'
AND m.organization_id = b.organization_id
AND alternate_bom_designator IS NULL

Restart Middle Tier

sh adapcctl.sh start apps/apps_password

sh adapcctl.sh stop apps/apps_password

Linking PO and GL Journal

SELECT jeh.*
FROM gl_import_references gli
, xla_ae_lines xll
, xla_ae_headers xlh
, xla_distribution_links xld
, gl_je_lines gl
, po_lines_all pol
, po_line_locations_all pll
, po_distributions_all pod
WHERE 1=1
AND pod.line_location_id = pll.line_location_id
AND pll.po_line_id = pol.po_line_id
AND pol.po_header_id = poh.po_header_id
and gli.gl_sl_link_table = xll.gl_sl_link_table
AND gli.gl_sl_link_id = xll.gl_sl_link_id
AND xll.ae_header_id = xlh.ae_header_id
AND xld.ae_header_id = xlh.ae_header_id
AND jel.je_header_id = gli.je_header_id
AND jel.je_line_num = gli.je_line_num
AND xld.source_distribution_id_num_1 = pod.po_distribution_id
AND xld.source_distribution_type = 'PO_DISTRIBUTIONS_ALL'
AND xll.gl_sl_link_table = 'XLAJEL'

Linking Receiving and GL journal

select jel.*
FROM gl_import_references gli
, xla_ae_lines xll
, xla_ae_headers xlh
, xla_distribution_links xld
, rcv_receiving_sub_ledger rsl
, gl_je_lines jel
WHERE gli.gl_sl_link_table = xll.gl_sl_link_table
AND gli.gl_sl_link_id = xll.gl_sl_link_id
AND xll.ae_header_id = xlh.ae_header_id
AND xld.ae_header_id = xlh.ae_header_id
AND jel.je_header_id = gli.je_header_id
AND jel.je_line_num = gli.je_line_num
AND xld.source_distribution_id_num_1 = rsl.rcv_sub_ledger_id
AND xld.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
AND xll.gl_sl_link_table = 'XLAJEL'

Linking AP Payment and GL Journal

SELECT api.invoice_num
, jel.accounted_cr
, jel.creation_date
, jeh.*
FROM gl_import_references gli
, xla_ae_lines xll
, xla_ae_headers xlh
, xla_distribution_links xld
, ap_invoices_all api
, ap_invoice_lines_all apl
, ap_invoice_distributions_all adp
, gl_je_lines jel
, gl_je_headers jeh
WHERE 1 = 1
AND api.invoice_id = apl.invoice_id
AND apl.invoice_id = adp.invoice_id
AND apl.line_number = adp.invoice_line_number
AND gli.gl_sl_link_table = xll.gl_sl_link_table
AND gli.gl_sl_link_id = xll.gl_sl_link_id
AND xll.ae_header_id = xlh.ae_header_id
AND xld.ae_header_id = xlh.ae_header_id
AND jel.je_header_id = jeh.je_header_id
AND jel.je_header_id = gli.je_header_id
AND jel.je_line_num = gli.je_line_num
AND xld.source_distribution_id_num_1 = adp.invoice_distribution_id
AND xld.source_distribution_type = 'AP_PMT_DIST'
AND xll.gl_sl_link_table = 'XLAJEL'

unix

grep -ril 'group by' $APPL_TOP/cust/*/*/sql/*.sql | wc -l

PO till Cheque payment

SELECT
A.ORG_ID "ORG ID",
E.VENDOR_NAME "VENDOR NAME",
UPPER(E.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE",
F.VENDOR_SITE_CODE "VENDOR SITE",
F.ADDRESS_LINE1 "ADDRESS",
F.CITY "CITY",
F.COUNTRY "COUNTRY",
TO_CHAR(TRUNC(D.CREATION_DATE)) "PO DATE",
D.SEGMENT1 "PO NUMBER",
D.TYPE_LOOKUP_CODE "PO TYPE",
C.QUANTITY_ORDERED "QTY ORDERED",
C.QUANTITY_CANCELLED "QTY CANCALLED",
G.ITEM_DESCRIPTION "ITEM DESCRIPTION",
G.UNIT_PRICE "UNIT PRICE",
(NVL(C.QUANTITY_ORDERED,0)-
NVL(C.QUANTITY_CANCELLED,0))*NVL(G.UNIT_PRICE,0) "PO Line Amount",
(SELECT
DECODE(PH.APPROVED_FLAG, 'Y', 'Approved')
FROM PO.PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = D.PO_HEADER_ID) "PO STATUS",
A.INVOICE_TYPE_LOOKUP_CODE "INVOICE TYPE",
A.INVOICE_AMOUNT "INVOICE AMOUNT",
TO_CHAR(TRUNC(A.INVOICE_DATE)) "INVOICE DATE",
A.INVOICE_NUM "INVOICE NUMBER",
(SELECT
DECODE(X.MATCH_STATUS_FLAG, 'A', 'Approved')
FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL X
WHERE X.INVOICE_DISTRIBUTION_ID =
B.INVOICE_DISTRIBUTION_ID)"Invoice Approved?",
A.AMOUNT_PAID,
H.AMOUNT,
I.CHECK_NUMBER "CHEQUE NUMBER",
TO_CHAR(TRUNC(I.CHECK_DATE)) "PAYMENT DATE"
FROM AP.AP_INVOICES_ALL A,
AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
PO.PO_DISTRIBUTIONS_ALL C,
PO.PO_HEADERS_ALL D,
PO.PO_VENDORS E,
PO.PO_VENDOR_SITES_ALL F,
PO.PO_LINES_ALL G,
AP.AP_INVOICE_PAYMENTS_ALL H,
AP.AP_CHECKS_ALL I
WHERE A.INVOICE_ID = B.INVOICE_ID
AND B.PO_DISTRIBUTION_ID = C. PO_DISTRIBUTION_ID (+)
AND C.PO_HEADER_ID = D.PO_HEADER_ID (+)
AND E.VENDOR_ID (+) = D.VENDOR_ID
AND F.VENDOR_SITE_ID (+) = D.VENDOR_SITE_ID
AND D.PO_HEADER_ID = G.PO_HEADER_ID
AND C.PO_LINE_ID = G.PO_LINE_ID
AND A.INVOICE_ID = H.INVOICE_ID
AND H.CHECK_ID = I.CHECK_ID
AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
AND C.PO_HEADER_ID IS NOT NULL
AND A.PAYMENT_STATUS_FLAG = 'Y'
AND D.TYPE_LOOKUP_CODE != 'BLANKET';

PO With there approval , invoice and payment details

select
a.org_id "ORG ID",
E.SEGMENT1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER(e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE",
f.ADDRESS_LINE1 "ADDRESS",
f.city "CITY",
f.country "COUNTRY",
to_char(trunc(d.CREATION_DATE)) "PO Date",
d.segment1 "PO NUM",
d.type_lookup_code "PO Type",
c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED",
g.item_id "ITEM ID" ,
g.item_description "ITEM DESCRIPTION",
g.unit_price "UNIT PRICE",
(NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount",
(select
decode(ph.approved_FLAG, 'Y', 'Approved')
from po.po_headers_all ph
where ph.po_header_ID = d.po_header_id)"PO Approved?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(select
decode(x.MATCH_STATUS_FLAG, 'A', 'Approved')
from ap.ap_invoice_distributions_all x
where x.INVOICE_DISTRIBUTION_ID = b.invoice_distribution_id)"Invoice Approved?",
a.amount_paid,
h.amount,
h.check_id,
h.invoice_payment_id "Payment Id",
i.check_number "Cheque Number",
to_char(trunc(i.check_DATE)) "PAYMENT DATE"

FROM AP.AP_INVOICES_ALL A,
AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
PO.PO_DISTRIBUTIONS_ALL C,
PO.PO_HEADERS_ALL D,
PO.PO_VENDORS E,
PO.PO_VENDOR_SITES_ALL F,
PO.PO_LINES_ALL G,
AP.AP_INVOICE_PAYMENTS_ALL H,
AP.AP_CHECKS_ALL I
where a.invoice_id = b.invoice_id
and b.po_distribution_id = c. po_distribution_id (+)
and c.po_header_id = d.po_header_id (+)
and e.vendor_id (+) = d.VENDOR_ID
and f.vendor_site_id (+) = d.vendor_site_id
and d.po_header_id = g.po_header_id
and c.po_line_id = g.po_line_id
and a.invoice_id = h.invoice_id
and h.check_id = i.check_id
and f.vendor_site_id = i.vendor_site_id
and c.PO_HEADER_ID is not null
and a.payment_status_flag = 'Y'
and d.type_lookup_code != 'BLANKET'

PO without Requisition

select
prh.segment1 "PR NUM",
trunc(prh.creation_date) "CREATED ON",
trunc(prl.creation_date) "Line Creation Date" ,
prl.line_num "Seq #",
msi.segment1 "Item Num",
prl.item_description "Description",
prl.quantity "Qty",
trunc(prl.need_by_date) "Required By",
ppf1.full_name "REQUESTOR",
ppf2.agent_name "BUYER"
from
po.po_requisition_headers_all prh,
po.po_requisition_lines_all prl,
apps.per_people_f ppf1,
(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,
po.po_req_distributions_all prd,
inv.mtl_system_items_b msi,
po.po_line_locations_all pll,
po.po_lines_all pl,
po.po_headers_all ph
WHERE
prh.requisition_header_id = prl.requisition_header_id
and prl.requisition_line_id = prd.requisition_line_id
and ppf1.person_id = prh.preparer_id
and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date
and ppf2.agent_id(+) = msi.buyer_id
and msi.inventory_item_id = prl.item_id
and msi.organization_id = prl.destination_organization_id
and pll.line_location_id(+) = prl.line_location_id
and pll.po_header_id = ph.po_header_id(+)
AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)
AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
AND PLL.LINE_LOCATION_ID IS NULL
AND PRL.CLOSED_CODE IS NULL
AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'
ORDER BY 1,2

PR to PO

select distinct u.description "Requestor",
porh.segment1 as "Req Number",
trunc(porh.Creation_Date) "Created On",
pord.LAST_UPDATED_BY,
porh.Authorization_Status "Status",
porh.Description "Description",
poh.segment1 "PO Number",
trunc(poh.Creation_date) "PO Creation Date",
poh.AUTHORIZATION_STATUS "PO Status",
trunc(poh.Approved_Date) "Approved Date"
from apps.po_headers_all poh,
apps.po_distributions_all pod,
apps.po_req_distributions_all pord,
apps.po_requisition_lines_all porl,
apps.po_requisition_headers_all porh,
apps.fnd_user u
where porh.requisition_header_id = porl.requisition_header_id
and porl.requisition_line_id = pord.requisition_line_id
and pord.distribution_id = pod.req_distribution_id(+)
and pod.po_header_id = poh.po_header_id(+)
and porh.created_by = u.user_id
order by 2

Purchase Requisition without PO

select
prh.segment1 "PR NUM",
trunc(prh.creation_date) "CREATED ON",
trunc(prl.creation_date) "Line Creation Date" ,
prl.line_num "Seq #",
msi.segment1 "Item Num",
prl.item_description "Description",
prl.quantity "Qty",
trunc(prl.need_by_date) "Required By",
ppf1.full_name "REQUESTOR",
ppf2.agent_name "BUYER"
from
po.po_requisition_headers_all prh,
po.po_requisition_lines_all prl,
apps.per_people_f ppf1,
(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,
po.po_req_distributions_all prd,
inv.mtl_system_items_b msi,
po.po_line_locations_all pll,
po.po_lines_all pl,
po.po_headers_all ph
WHERE
prh.requisition_header_id = prl.requisition_header_id
and prl.requisition_line_id = prd.requisition_line_id
and ppf1.person_id = prh.preparer_id
and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date
and ppf2.agent_id(+) = msi.buyer_id
and msi.inventory_item_id = prl.item_id
and msi.organization_id = prl.destination_organization_id
and pll.line_location_id(+) = prl.line_location_id
and pll.po_header_id = ph.po_header_id(+)
AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)
AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
AND PLL.LINE_LOCATION_ID IS NULL
AND PRL.CLOSED_CODE IS NULL
AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'
ORDER BY 1,2

Purchase Order and PR

select r.segment1 "Req Num",
p.segment1 "PO Num"
from po_headers_all p,
po_distributions_all d,
po_req_distributions_all rd,
po_requisition_lines_all rl,
po_requisition_headers_all r
where p.po_header_id = d.po_header_id
and d.req_distribution_id = rd.distribution_id
and rd.requisition_line_id = rl.requisition_line_id
and rl.requisition_header_id = r.requisition_header_id

Cancel PR

select prh.REQUISITION_HEADER_ID,
prh.PREPARER_ID ,
prh.SEGMENT1 "REQ NUM",
trunc(prh.CREATION_DATE),
prh.DESCRIPTION,
prh.NOTE_TO_AUTHORIZER
from apps.Po_Requisition_headers_all prh,
apps.po_action_history pah
where Action_code='CANCEL'
and pah.object_type_code='REQUISITION'
and pah.object_id=prh.REQUISITION_HEADER_ID

Asset Seelcting Query

SELECT b.book_type_code "Asset Book Type"
, l.segment1 loc1
, l.segment2 loc2
, l.attribute1 "site "
, a.asset_number "Asset Number"
, a.asset_type "Asset Type"
, a.tag_number "Tag Number"
, b.COST "Cost"
, ds.deprn_reserve
, (b.COST - ds.deprn_reserve) net_book_value
, get_account( da.ASSET_COST_ACCOUNT_CCID ) "Asset Cost Account" ,
get_account( da.ASSET_CLEARING_ACCOUNT_CCID ) "Asset Clearing Account" ,
get_account( da.DEPRN_EXPENSE_ACCOUNT_CCID ) "Depreciation Expense Account" ,
get_account( da.DEPRN_RESERVE_ACCOUNT_CCID ) "Depreciation Reserve Account",
get_account( da.CIP_COST_ACCOUNT_CCID ) "CIP Cost Account",
get_account( da.CIP_CLEARING_ACCOUNT_CCID ) "CIP Clearing Account",
get_account( da.NBV_RETIRED_GAIN_CCID ) "Net Book Value Retired Gain",
get_account( da.NBV_RETIRED_LOSS_CCID ) "Net Book Value Retired Loss",
get_account( da.PROCEEDS_SALE_GAIN_CCID ) "Proceeds Sale Gain",
get_account( da.PROCEEDS_SALE_LOSS_CCID ) "Proceeds Sale Loss",
get_account( da.COST_REMOVAL_GAIN_CCID ) "Cost Removal Gain",
get_account( da.COST_REMOVAL_LOSS_CCID ) "Cost Removal Loss",
get_account( da.PROCEEDS_SALE_CLEARING_CCID ) "Proceed Sale Clearing",
get_account( da.COST_REMOVAL_CLEARING_CCID ) "Cost Removal Clearing",
get_account( da.REVAL_RSV_GAIN_ACCOUNT_CCID ) "Revaluation Reserve Ret Gain",
get_account( da.REVAL_RSV_LOSS_ACCOUNT_CCID ) "Revaluation Reserve Ret Loss",
get_account( da.DEFERRED_EXP_ACCOUNT_CCID ) "Deferred Depreciation Expense",
get_account( da.DEFERRED_RSV_ACCOUNT_CCID ) "Deferred Depreciation Reserve",
get_account( da.DEPRN_ADJ_ACCOUNT_CCID ) "Depreciation Adjustment",
get_account( da.REVAL_AMORT_ACCOUNT_CCID ) "Revaluation Amortization",
get_account( da.REVAL_RSV_ACCOUNT_CCID ) "Revaluation Reserve",
get_account( da.BONUS_EXP_ACCOUNT_CCID ) "Bonus Depreciation Expense",
get_account( da.BONUS_RSV_ACCOUNT_CCID ) "Bonus Depreciation Reserve"
FROM fa_additions_b a
, fa_books b
, fa_book_controls c
, fa_distribution_history d
, fa_locations l
, fa_deprn_summary ds
, fa_distribution_accounts da
WHERE a.asset_id = b.asset_id
AND b.book_type_code = c.book_type_code
AND c.set_of_books_id = 2
AND c.book_class = 'CORPORATE'
AND in_use_flag = 'YES'
AND b.date_ineffective IS NULL
AND d.asset_id = a.asset_id
AND l.location_id = d.location_id
AND ds.book_type_code = b.book_type_code
AND ds.asset_id = b.asset_id
AND d.distribution_id = da.distribution_id
AND d.book_type_code = b.book_type_code
AND d.book_type_code = da.book_type_code
AND d.date_effective = (SELECT MAX (date_effective)
FROM fa_distribution_history
WHERE asset_id = d.asset_id
AND book_type_code = d.book_type_code)
AND ds.period_counter = (SELECT MAX (ds1.period_counter)
FROM fa_deprn_summary ds1
WHERE ds1.asset_id = ds.asset_id
AND ds1.book_type_code = ds.book_type_code)
ORDER BY asset_number

-------------------------------------------------------

MC
SELECT b.book_type_code
, l.segment1 loc1
, l.segment2 loc2
, l.attribute1 site
, a.asset_number
, a.asset_type
, a.tag_number
, b.COST
, ds.deprn_reserve
, (b.COST - ds.deprn_reserve) net_book_value
, get_account( da.ASSET_COST_ACCOUNT_CCID ) "Asset Cost Account" ,
get_account( da.ASSET_CLEARING_ACCOUNT_CCID ) "Asset Clearing Account" ,
get_account( da.DEPRN_EXPENSE_ACCOUNT_CCID ) "Depreciation Expense Account" ,
get_account( da.DEPRN_RESERVE_ACCOUNT_CCID ) "Depreciation Reserve Account",
get_account( da.CIP_COST_ACCOUNT_CCID ) "CIP Cost Account",
get_account( da.CIP_CLEARING_ACCOUNT_CCID ) "CIP Clearing Account",
get_account( da.NBV_RETIRED_GAIN_CCID ) "Net Book Value Retired Gain",
get_account( da.NBV_RETIRED_LOSS_CCID ) "Net Book Value Retired Loss",
get_account( da.PROCEEDS_SALE_GAIN_CCID ) "Proceeds Sale Gain",
get_account( da.PROCEEDS_SALE_LOSS_CCID ) "Proceeds Sale Loss",
get_account( da.COST_REMOVAL_GAIN_CCID ) "Cost Removal Gain",
get_account( da.COST_REMOVAL_LOSS_CCID ) "Cost Removal Loss",
get_account( da.PROCEEDS_SALE_CLEARING_CCID ) "Proceed Sale Clearing",
get_account( da.COST_REMOVAL_CLEARING_CCID ) "Cost Removal Clearing",
get_account( da.REVAL_RSV_GAIN_ACCOUNT_CCID ) "Revaluation Reserve Ret Gain",
get_account( da.REVAL_RSV_LOSS_ACCOUNT_CCID ) "Revaluation Reserve Ret Loss",
get_account( da.DEFERRED_EXP_ACCOUNT_CCID ) "Deferred Depreciation Expense",
get_account( da.DEFERRED_RSV_ACCOUNT_CCID ) "Deferred Depreciation Reserve",
get_account( da.DEPRN_ADJ_ACCOUNT_CCID ) "Depreciation Adjustment",
get_account( da.REVAL_AMORT_ACCOUNT_CCID ) "Revaluation Amortization",
get_account( da.REVAL_RSV_ACCOUNT_CCID ) "Revaluation Reserve",
get_account( da.BONUS_EXP_ACCOUNT_CCID ) "Bonus Depreciation Expense",
get_account( da.BONUS_RSV_ACCOUNT_CCID ) "Bonus Depreciation Reserve"
FROM fa_additions_b a
, fa_mc_books b
, fa_mc_book_controls c
, fa_distribution_history d
, fa_locations l
, fa_mc_deprn_summary ds
, fa_distribution_accounts da
WHERE a.asset_id = b.asset_id
AND b.book_type_code = c.book_type_code
AND c.set_of_books_id = 4
--and c.book_class = 'CORPORATE'
AND in_use_flag = 'YES'
AND b.date_ineffective IS NULL
AND d.asset_id = a.asset_id
AND l.location_id = d.location_id
AND ds.book_type_code = b.book_type_code
AND ds.asset_id = b.asset_id
AND d.distribution_id = da.distribution_id
AND d.book_type_code = b.book_type_code
AND d.book_type_code = da.book_type_code
AND d.date_effective = (SELECT MAX (date_effective)
FROM fa_distribution_history
WHERE asset_id = d.asset_id
AND book_type_code = d.book_type_code)
AND ds.period_counter = (SELECT MAX (ds1.period_counter)
FROM fa_mc_deprn_summary ds1
WHERE ds1.asset_id = ds.asset_id
AND ds1.book_type_code = ds.book_type_code)
ORDER BY asset_number

GET Code Combination Segments

CREATE OR REPLACE FUNCTION APPS.get_account_seg (p_ccid number)
RETURN VARCHAR2 AS
l_segments gl_code_combinations_kfv.concatenated_segments%TYPE;
BEGIN
SELECT kfv.concatenated_segments
into l_segments
FROM gl_code_combinations_kfv kfv
WHERE code_combination_id = p_ccid;
return l_segments;
exception
when others then
return null;

END;
/

GET Code Combination ID

CREATE OR REPLACE FUNCTION APPS.get_account_id (p_ccid varchar2)
RETURN NUMBER AS
l_segments gl_code_combinations_kfv.code_combination_id%TYPE;
BEGIN
SELECT code_combination_id
into l_segments
FROM gl_code_combinations_kfv kfv
WHERE kfv.concatenated_segments = p_ccid;
return l_segments;
exception
when others then
return null;
END;