All modules Interface Tables

GL INTERFACE TABLES

TNAME TABTYPE
------------------------------ ------- ----------------------
GL_BUDGET_INTERFACE TABLE
GL_DAILY_RATES_INTERFACE TABLE
GL_IEA_INTERFACE TABLE
GL_INTERFACE TABLE
GL_INTERFACE_CONTROL TABLE
GL_INTERFACE_HISTORY TABLE

AP INTERFACE TABLES

TNAME TABTYPE
------------------------------ ------- ------------------------
AP_INTERFACE_CONTROLS TABLE
AP_INTERFACE_REJECTIONS TABLE
AP_INVOICES_INTERFACE TABLE
AP_INVOICE_LINES_INTERFACE TABLE


AR INTERFACE TABLES

TNAME TABTYPE
------------------------------ --------------------------------------
AR_PAYMENTS_INTERFACE_ALL TABLE
AR_TAX_INTERFACE TABLE
HZ_DQM_SYNC_INTERFACE TABLE
HZ_PARTY_INTERFACE TABLE
HZ_PARTY_INTERFACE_ERRORS TABLE
RA_CUSTOMERS_INTERFACE_ALL TABLE
RA_INTERFACE_DISTRIBUTIONS_ALL TABLE
RA_INTERFACE_ERRORS_ALL TABLE
RA_INTERFACE_LINES_ALL TABLE
RA_INTERFACE_SALESCREDITS_ALL TABLE


FA INTERFACE TABLES

TNAME TABTYPE
------------------------------ ------- ----------------------
FA_BUDGET_INTERFACE TABLE
FA_INV_INTERFACE TABLE
FA_PRODUCTION_INTERFACE TABLE
FA_TAX_INTERFACE TABLE

INVENTORY INTERFACE TABLES

TNAME TABTYPE
------------------------------ ------- ------------------------------------
MTL_CC_ENTRIES_INTERFACE TABLE
MTL_CC_INTERFACE_ERRORS TABLE
MTL_CI_INTERFACE TABLE
MTL_CI_XREFS_INTERFACE TABLE
MTL_COPY_ORG_INTERFACE TABLE
MTL_CROSS_REFERENCES_INTERFACE TABLE
MTL_DEMAND_INTERFACE TABLE
MTL_DESC_ELEM_VAL_INTERFACE TABLE
MTL_EAM_ASSET_NUM_INTERFACE TABLE
MTL_EAM_ATTR_VAL_INTERFACE TABLE
MTL_INTERFACE_ERRORS TABLE

TNAME TABTYPE
------------------------------ ------- --------------------------------------
MTL_INTERFACE_PROC_CONTROLS TABLE
MTL_ITEM_CATEGORIES_INTERFACE TABLE
MTL_ITEM_CHILD_INFO_INTERFACE TABLE
MTL_ITEM_REVISIONS_INTERFACE TABLE
MTL_ITEM_SUB_INVS_INTERFACE TABLE
MTL_OBJECT_GENEALOGY_INTERFACE TABLE
MTL_RELATED_ITEMS_INTERFACE TABLE
MTL_RESERVATIONS_INTERFACE TABLE
MTL_RTG_ITEM_REVS_INTERFACE TABLE
MTL_SECONDARY_LOCS_INTERFACE TABLE
MTL_SERIAL_NUMBERS_INTERFACE TABLE

TNAME TABTYPE
------------------------------ ------- ------------------------------------
MTL_SO_RMA_INTERFACE TABLE
MTL_SYSTEM_ITEMS_INTERFACE TABLE
MTL_TRANSACTIONS_INTERFACE TABLE
MTL_TRANSACTION_LOTS_INTERFACE TABLE
MTL_TXN_COST_DET_INTERFACE TABLE

PO INTERFACE TABLES

TNAME TABTYPE
------------------------------ ------- -------------------------
PO_DISTRIBUTIONS_INTERFACE TABLE
PO_HEADERS_INTERFACE TABLE
PO_INTERFACE_ERRORS TABLE
PO_LINES_INTERFACE TABLE
PO_REQUISITIONS_INTERFACE_ALL TABLE
PO_REQ_DIST_INTERFACE_ALL TABLE
PO_RESCHEDULE_INTERFACE TABLE
RCV_HEADERS_INTERFACE TABLE
RCV_LOTS_INTERFACE TABLE
RCV_SERIALS_INTERFACE TABLE
RCV_TRANSACTIONS_INTERFACE TABLE

BOM INTERFACE TABLES

TNAME TABTYPE
------------------------------ ------- ----------------------------------
BOM_BILL_OF_MTLS_INTERFACE TABLE
BOM_INTERFACE_DELETE_GROUPS TABLE
BOM_INVENTORY_COMPS_INTERFACE TABLE
BOM_OP_RESOURCES_INTERFACE TABLE
BOM_OP_ROUTINGS_INTERFACE TABLE
BOM_OP_SEQUENCES_INTERFACE TABLE
BOM_REF_DESGS_INTERFACE TABLE
BOM_SUB_COMPS_INTERFACE TABLE
CST_COMP_SNAP_INTERFACE TABLE
CST_INTERFACE_ERRORS TABLE
CST_ITEM_COSTS_INTERFACE TABLE
CST_ITEM_CST_DTLS_INTERFACE TABLE
CST_PC_COST_DET_INTERFACE TABLE
CST_PC_ITEM_COST_INTERFACE TABLE

WIP INTERFACE TABLES

TNAME TABTYPE
------------------------------ ------- --------------------------
WIP_COST_TXN_INTERFACE TABLE
WIP_INTERFACE_ERRORS TABLE
WIP_JOB_DTLS_INTERFACE TABLE
WIP_JOB_SCHEDULE_INTERFACE TABLE
WIP_MOVE_TXN_INTERFACE TABLE
WIP_SCHEDULING_INTERFACE TABLE
WIP_TXN_INTERFACE_ERRORS TABLE

ORDER MANAGEMENT INTERFACE TABLES

TNAME TABTYPE
------------------------------ ------- -----------------------------------
SO_CONFIGURATIONS_INTERFACE TABLE
SO_HEADERS_INTERFACE_ALL TABLE
SO_HEADER_ATTRIBUTES_INTERFACE TABLE
SO_LINES_INTERFACE_ALL TABLE
SO_LINE_ATTRIBUTES_INTERFACE TABLE
SO_LINE_DETAILS_INTERFACE TABLE
SO_PRICE_ADJUSTMENTS_INTERFACE TABLE
SO_SALES_CREDITS_INTERFACE TABLE
SO_SERVICE_DETAILS_INTERFACE TABLE
WSH_DELIVERIES_INTERFACE TABLE
WSH_FREIGHT_CHARGES_INTERFACE TABLE
WSH_PACKED_CONTAINER_INTERFACE TABLE

Read More

PO Interface

PO_HEADERS_INTERFACE
is the interface table that imports header information from e– Commerce Gateway for blanket purchase orders and catalog quotations.
INTERFACE_HEADER_ID
BATCH_ID
INTERFACE_SOURCE_CODE

PO_LINES_INTERFACE
is the interface table that imports lines information from e– commerce Gateway for blanket purchase orders and catalog quotations.
INTERFACE_LINE_ID
INTERFACE_HEADER_ID
ACTION NULL Action to be completed: New or Add
GROUP_CODE (Null) Indicates the grouping of the requisition lines

PO_DISTRIBUTIONS_INTERFACE
is the interface table that imports distribution information from e– Commerce Gateway for blanket purchase orders and catalog quotations.
INTERFACE_HEADER_ID
INTERFACE_LINE_ID
INTERFACE_DISTRIBUTION_ID
ORG_ID

PO_REQUISITIONS_INTERFACE_ALL
contains requisition information from other applications. Import feature uses this information to create new requisition headers, lines and distributions.
TRANSACTION_ID (PK) Transaction unique identifier
PROCESS_FLAG NULL Transaction processing state

PO_REQ_DIST_INTERFACE_ALL
Is the interface table that creates multiple distributions using Requisition Import.
ACCRUAL_ACCOUNT_ID Unique identifier for the General Ledger accrual account
ALLOCATION_TYPE Specifies the method of allocation across distributions. Can be PERCENT.
ALLOCATION_VALUE Allocation split value
BATCH_ID Import batch identifier
BUDGET_ACCOUNT_ID Unique identifier for the General Ledger budget account
CHARGE_ACCOUNT_ID Unique identifier for the General Ledger charge account

RCV_HEADERS_INTERFACE
is the interface table that stores receiving header information.
HEADER_INTERFACE_ID Interface EDI header unique identifier
GROUP_ID NULL Interface group for set processing
EDI_CONTROL_NUM EDI transaction control number if data
is sent via EDI
PROCESSING_STATUS_CODE Processing status of the interface
header row
RECEIPT_SOURCE_CODE Source type of the shipment
ASN_TYPE NULL The document type: values are
ASN, ASBN, or RECEIVE
TRANSACTION_TYPE The transaction purpose code: values are
NEW, REPLACE, ADD,or CANCEL

RCV_LOTS_INTERFACE
Holds temporary lot number transaction records for a parent record in the RCV_TRANSACTIONS_INTERFACE table.

RCV_TRANSACTIONS_INTERFACE
stores information about receiving transactions that are waiting to be processed by the receiving transaction processor or were rejected due to an error when the transaction processor attempted to process the transaction.
TRANSACTION_TYPE
TRANSACTION_DATE
PROCESSING_STATUS_CODE

Interface Table
--PO_REQUISITION_INTERFACE_ALL
Program Name to Launch the PO Import program -Requisition Import program
In the first phase, the program validates your data and derives or defaults additional information. The program generates an error message for every validation that fails and creates a row
PO_REQUISITIONS_INTERFACE_ALL
It contains requisition information from other applications. Each row includes all the information necessary to create approved or unapproved requisitions in Oracle Purchasing.
The Oracle Purchasing Requisition Import feature uses this information to create new requisition headers, lines and distributions.

PO_REQUISITIONS_INTERFACE_ALL
table are identical to the corresponding columns in the
PO_REQUISITIONS_HEADERS_ALL,
PO_REQUISITION_LINES_ALL and PO_REQ_DISTRIBUTIONS_ALL
tables.

Po_requisition_interface_all
INTERFACE_SOURCE_CODE --VDP
,SOURCE_TYPE_CODE --INVENTORY
,DESTINATION_TYPE_CODE --EXPENSE
,AUTHORIZATION_STATUS --Status Always Approved we will get ..
,REQ_NUMBER_SEGMENT1 --Request id
,PREPARER_ID -- ASSOCIATE ID comes from VDP
,LINE_TYPE_ID
,LINE_TYPE --vArchar Requisition line type name
,QUANTITY --Quantity
,UNIT_PRICE
,CHARGE_ACCOUNT_ID -- to be hard coded based on the line type
,DESTINATION_ORGANIZATION_ID --v-org_id
,DELIVER_TO_LOCATION_ID -- location id
,DELIVER_TO_REQUESTOR_ID -- ASSOCIATE ID Same as Prepare_id
,ITEM_ID -- item code
,ITEM_DESCRIPTION --Based on the item code description is taken
,CATEGORY_ID
,UOM_CODE
,CURRENCY_CODE --if this is supplied then next two should be filled
,RATE
,RATE_DATE
,RATE_TYPE
If you use Oracle Master Scheduling/MRP or a non– Oracle MRP system with Oracle Purchasing, you may find that you need to reschedule requisitions as your planning requirements change.

Reschedule Interface Table Since you have already loaded your requisitions into Oracle Purchasing, you simply need to identify for Oracle Purchasing the requisition lines you want to reschedule. After you identify each line to reschedule, you can update the quantity and the need– by date for the corresponding requisition line.
PO_RESCHEDULE_INTERFACE table for each change you want to make to a requisition. Each row includes the requisition line identifier, the new quantity, and the new need– by date for the requisition line. You run the Requisition Reschedule program to implement the changes. Import Requisitions from the External System to Oracle Purchasing Write a Import Program to load the external data Dump data into the some interface table .Validate the data through some PL/SQL
(Applying Business Logic---
Run Import Program
Requisition is imported and created as many
number of requisition as successful records .Others -if error then run exceptional report to see the reason of failure.

Read More

PO Queries

1. You need to list out all Internal Requisitions that do not have an associated Internal Sales order.

Internal Requisitions without Sales order

Select RQH.SEGMENT1 REQ_NUM,

RQL.LINE_NUM,

RQL.REQUISITION_HEADER_ID ,

RQL.REQUISITION_LINE_ID,

RQL.ITEM_ID ,

RQL.UNIT_MEAS_LOOKUP_CODE ,

RQL.UNIT_PRICE ,

RQL.QUANTITY ,

RQL.QUANTITY_CANCELLED,

RQL.QUANTITY_DELIVERED ,

RQL.CANCEL_FLAG ,

RQL.SOURCE_TYPE_CODE ,

RQL.SOURCE_ORGANIZATION_ID ,

RQL.DESTINATION_ORGANIZATION_ID,

RQH.TRANSFERRED_TO_OE_FLAG

from

PO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQH

where

RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID

and RQL.SOURCE_TYPE_CODE = 'INVENTORY'

and RQL.SOURCE_ORGANIZATION_ID is not null

and not exists (select 'existing internal order'

from OE_ORDER_LINES_ALL LIN

where LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID

and LIN.SOURCE_DOCUMENT_TYPE_ID = 10)

ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;

2. You want to display what requisition and PO are linked(Relation with Requisition and PO )

Requisition and PO

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 

3. You need to list out all cancel Requisitions

Cancel Requisition

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 

4. You need to list those PR which havn’t auto created to PO.(Purchase Requisition without a Purchase Order)

PR 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

5. You need to list all information form PR to PO …as a requisition moved from different stages till converting into PR. This query capture all details related to that PR to PO.

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 

6.Identifying all PO’s which does not have any PR’s

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

7. Relation between Requisition and PO tables

PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_ID
PO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1
PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_ID
PO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID)
PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1

What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req.

8.You need to find table which hold PO Approval path…

These two table keeps the data:

• PO_APPROVAL_LIST_HEADERS
• PO_APPROVAL_LIST_LINES

9. List all the PO’s with there approval ,invoice and Payment Details

List PO’s with Approval , invoice and Payment info

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' 

10.You need to know the link to GL_JE_LINES table for purchasing accrual and budgetary control actions..

The budgetary (encumbrance) and accrual actions in the purchasing module generate records that will be imported into GL for the corresponding accrual and budgetary journals.

The following reference fields are used to capture and keep PO information in the GL_JE_LINES table.

These reference fields are populated when the Journal source (JE_SOURCE in GL_JE_HEADERS) is
Purchasing.

Budgetary Records from PO (These include reservations, reversals and cancellations):

• REFERENCE_1- Source (PO or REQ)
• REFERENCE_2- PO Header ID or Requisition Header ID (from po_headers_all.po_header_id or
  po_requisition_headers_all.requisition_header_id)
• REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id or
  po_req_distributions_all.distribution_id)
• REFERENCE_4- Purchase Order or Requisition number (from po_headers_all.segment1 or
  po_requisition_headers_all.segment1)
• REFERENCE_5- (Autocreated Purchase Orders only) Backing requisition number (from po_requisition_headers_all.segment1)

Accrual Records from PO:

• REFERENCE_1- Source (PO)
• REFERENCE_2- PO Header ID (from po_headers_all.po_header_id)
• REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id
• REFERENCE_4- Purchase Order number (from po_headers_all.segment1)
• REFERENCE_5- (ON LINE ACCRUALS ONLY) Receiving Transaction ID (from rcv_receiving_sub_ledger.rcv_transaction_id)

11. List me all open PO’s

LList all Open PO/span>’S

select 

h.segment1 "PO NUM", 

h.authorization_status "STATUS", 

l.line_num "SEQ NUM", 

ll.line_location_id, 

d.po_distribution_id , 

h.type_lookup_code "TYPE" 

from 

po.po_headers_all h, 

po.po_lines_all l, 

po.po_line_locations_all ll, 

po.po_distributions_all d 

where h.po_header_id = l.po_header_id 

and ll.po_line_id = l.po_Line_id 

and ll.line_location_id = d.line_location_id 

and h.closed_date is null 

and h.type_lookup_code not in ('QUOTATION') 

12.There are different authorization_status can a requisition have.

• Approved
• Cancelled
• In Process
• Incomplete
• Pre-Approved
• Rejected

and you should note: When we finally close the requisition from Requisition Summary form the authorization_status of the requisition does not change. Instead it’s closed_code becomes ‘FINALLY CLOSED’.

13. A standard Quotations one that you can tie back to a PO.
Navigate to RFQ -> Auto create -> enter a PO and reference it back.

Read More

AOL Quiries.

1 ) To get list of responsibilities

SELECT (SELECT application_short_name
FROM fnd_application fa
WHERE fa.application_id = frt.application_id) application,
frt.responsibility_id, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt;

2) To get Menus Associated with responsibility

SELECT DISTINCT a.responsibility_name, c.user_menu_name
FROM apps.fnd_responsibility_tl a,
apps.fnd_responsibility b,
apps.fnd_menus_tl c,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f
WHERE a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_name p_responsbility_name
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = 'US';

p_responsbility_name is the name of the responsbility_name to be passed

3)To get submenus and Function attached to this Main menu

SELECT c.prompt,
c.description
FROM apps.fnd_menus_tl a,
fnd_menu_entries_tl c
WHERE a.menu_id = c.menu_id
AND a.user_menu_name = p_menu_name

4) To get assigned responsibility to a user.

SELECT distinct u.user_id
, SUBSTR (u.user_name, 1, 30) user_name
,SUBSTR (r.responsibility_name, 1, 60) responsiblity
,SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60);


5)To get responsibility and attached request groups.

SELECT responsibility_name responsibility
, request_group_name,
frg.description
FROM fnd_request_groups frg,
fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name

6)Request Attached To Responsibility Listing

SELECT responsibility_name ,
frg.request_group_name,
fcpv.user_concurrent_program_name,
fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;

6)To get all request with application

SELECT fa.application_short_name,
fcpv.user_concurrent_program_name,
description,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
output_file_type,
program_type,
printer_name,
minimum_width,
minimum_length,
concurrent_program_name,
concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv,
fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY description

7)Request Status Listing

SELECT f.request_id ,
pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date
, floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ' HOURS ' ||floor((((f.actual_completion_date-f.actual_start_date)*24 *60*60) -floor(((f.actual_completion_date-f.actual_start_date)*24*60 *60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/ 3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference
,
DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.descriptio
n||']',p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase
, f.status_code
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;

Read More

Purchasing Quiries

1) CANCEL REQUISITIONS
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

2)INTERNAL REQUISITIONS THAT DO NOT HAVE AN ASSOCIATED INTERNAL SALES ORDER

SELECT
,RQH.SEGMENT1
,RQL.LINE_NUM
,RQL.REQUISITION_HEADER_ID
,RQL.REQUISITION_LINE_ID
,RQL.ITEM_ID
,RQL.UNIT_MEAS_LOOKUP_CODE
,RQL.UNIT_PRICE
,RQL.QUANTITY
,RQL.QUANTITY_CANCELLED
,RQL.QUANTITY_DELIVERED
,RQL.CANCEL_FLAG
,RQL.SOURCE_TYPE_CODE
,RQL.SOURCE_ORGANIZATION_ID
,RQL.DESTINATION_ORGANIZATION_ID
,RQH.TRANSFERRED_TO_OE_FLAG
FROM
,PO_REQUISITION_LINES_ALL RQL
,PO_REQUISITION_HEADERS_ALL RQH
WHERE
RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID and
RQL.SOURCE_TYPE_CODE = 'INVENTORY' and
RQL.SOURCE_ORGANIZATION_ID is not null
and not exists(select 'existing internal order']
from OE_ORDER_LINES_ALL LIN
where LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID and LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;

3)DISPLAY WHAT REQUISITION AND PO ARE LINKED
(Relation with Requisition and PO )
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

4)PURCHASE REQUISITION WITHOUT PO THAT MEANS A PR HAS NOT BEEN AUTOCREATED TO PO
(Purchase Requisition without a Purchase Order)

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.pl_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

3)ALL PO’s WITH APPROVAL, INVOICE, & 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'

4)ALL OPEN PO'S

SELECT
,h.segment1 "PO NUM"
,h.authorization_status "STATUS"
,l.line_num "SEQ NUM"
,ll.line_location_id
,d.po_distribution_id
,h.type_lookup_code "TYPE"
FROM
,po.po_headers_all h
,po.po_lines_all l
,po.po_line_locations_all ll
,po.po_distributions_all d
WHERE
h.po_header_id = l.po_header_id and
ll.po_line_id = l.po_Line_id and
ll.line_location_id = d.line_location_id and
h.closed_date is NULL and
h.type_lookup_code not in ('QUOTATION')

Read More

Chart of Accounts Structure Overview

GL : Chart of Accounts Structure Overview:-
--------------------------------------------------

CHART OF ACCOUNTS STRUCTURE

Gives an overview of the chart of accounts definitions and also status.
This is used when implementing multiple charts of accounts to ensure consistent setup across countries and between environments.
Where clause can be added or commented out to just look at specific countries.

I have tested in 11.5.10.2 Instance. This will work in all the 11i Instances.

SELECT FST.ID_FLEX_STRUCTURE_NAME
--, FST.DESCRIPTION
--, FST.ID_FLEX_NUM
--, FST.ID_FLEX_STRUCTURE_CODE
, FST.CROSS_SEGMENT_VALIDATION_FLAG"X-VAL"
, FST.FREEZE_STRUCTURED_HIER_FLAG"FZ-HIER"
, FST.FREEZE_FLEX_DEFINITION_FLAG"FZ-DEFN"
, FSEG.SEGMENT_NUM "SEG#"
, FSEG.SEGMENT_NAME "SEG NAME"
, VS.FLEX_VALUE_SET_NAME "VALUE SET"
, FSEG.FLEX_VALUE_SET_ID"VAL_SET_ID"
, FSEG.DEFAULT_TYPE"DEF TYPE"
, FSEG.DEFAULT_VALUE"DEF. VALUE"
, FSEG.ENABLED_FLAG"ENBLD"
, FSEG.REQUIRED_FLAG"REQD"
FROM FND_ID_FLEX_STRUCTURES_VL FST, FND_ID_FLEX_SEGMENTS FSEG, FND_FLEX_VALUE_SETS VS
WHERE FST.ID_FLEX_NUM = FSEG.ID_FLEX_NUM
AND FSEG.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID
AND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN ('HB')
AND FST.APPLICATION_ID = 101
AND FST.ID_FLEX_CODE = 'GL#'
ORDER BY 1, FSEG.SEGMENT_NUM

Read More

Chart of Account Segment Hierarchy Ranges

GL : Chart of Account Segment Hierarchy Ranges:-
----------------------------------------------------------

Chart of account segment hierarchy ranges and attributes for parent accounts

I have tested in 11.5.10.2 Instance. This will work in all the 11i Instances.

I have commented the SOB condition. Check your Instances SOB short-name and provide accordingly if you have multiple SOB in your business Structure.

I have given the Chart of account segment name as 'ABC' and Parent segment Value as XYZ. Check out the Values In your Instances and run accordingly.

SELECT FVS.FLEX_VALUE_SET_NAME"Value Set"
, FV.FLEX_VALUE
, NH.PARENT_FLEX_VALUE "Parent"
, FVT.DESCRIPTION
, NH.RANGE_ATTRIBUTE "Inc C or P?"
, NH.CHILD_FLEX_VALUE_LOW "From"
, NH.CHILD_FLEX_VALUE_HIGH "To"
, NH.PARENT_FLEX_VALUE || ' : ' ||NH.RANGE_ATTRIBUTE || ' : ' ||
NH.CHILD_FLEX_VALUE_LOW || ' -> ' ||NH.CHILD_FLEX_VALUE_HIGH "Hierarchy Range"
, SUBSTR(FV.COMPILED_VALUE_ATTRIBUTES,1,1)"Posting"
, SUBSTR(FV.COMPILED_VALUE_ATTRIBUTES,3,1)"Budgeting"
, SUBSTR(FV.COMPILED_VALUE_ATTRIBUTES,5,1)"Acc Type"
, FV.ENABLED_FLAG"Enabled"
, FV.SUMMARY_FLAG"Parent?"
, NH.LAST_UPDATE_DATE
, FV.HIERARCHY_LEVEL"Level"
FROM FND_FLEX_VALUE_NORM_HIERARCHY NH, FND_FLEX_VALUE_SETS FVS, FND_FLEX_VALUES_TL FVT, FND_FLEX_VALUES FV
WHERE FVS.FLEX_VALUE_SET_ID = FV.FLEX_VALUE_SET_ID
AND FVS.FLEX_VALUE_SET_ID = NH.FLEX_VALUE_SET_ID
AND FV.FLEX_VALUE_ID = FVT.FLEX_VALUE_ID
AND NH.PARENT_FLEX_VALUE(+) = FVT.FLEX_VALUE_MEANING
AND FVS.FLEX_VALUE_SET_ID = NH.FLEX_VALUE_SET_ID
AND FVS.FLEX_VALUE_SET_NAME LIKE '%ABC%' --- Chart of accounts segment name
-- AND SUBSTR(FVS.FLEX_VALUE_SET_NAME,4,2) IN ('HB')
AND FV.SUMMARY_FLAG = 'Y'
AND FV.FLEX_VALUE LIKE '%XYZ%' --- This is the parent segment values
-- AND NH.PARENT_FLEX_VALUE = '%%'
-- AND FV.ENABLED_FLAG = 'Y'
-- AND FV.HIERARCHY_LEVEL = '2'
ORDER BY 1,3

Read More

Operating Unit and Legal Entity Configuration

GL : Operating Unit and Legal Entity Configuration:-
-------------------------------------------------------------

LEGAL ENTITIERS & ORGANIZATIONS

Gives an overview of the legal entity and operating unit configuration across multiple OU
This is used when implementing multiple offices to ensure consistent setup across countries and between environments.
Where clause can be added or commented out to just look at specific countries.if consistent naming conventions have been used.

I have tested in 11.5.10.2 Instance. This will work in all the 11i Instances.

I have commented the SOB condition. Check your Instances SOB short-name and provide accordingly if you have multiple SOB in your business Structure.


SELECT HRO.ORGANIZATION_ID
, HRO.NAME
, HOI.ORG_INFORMATION_CONTEXT
, SOB2.NAME "LE SET OF BOOKS"
--, HOI.ORG_INFORMATION1
, HRO_LE.NAME "OU LEGAL ENT"
, HOI.ORG_INFORMATION2 "LE VAT CODES"
--, HOI.ORG_INFORMATION3
, SOB.NAME "OU SET OF BOOKS"
FROM HR_ALL_ORGANIZATION_UNITS_TL HRO, HR_ORGANIZATION_INFORMATION_V HOI, GL_SETS_OF_BOOKS SOB,GL_SETS_OF_BOOKS SOB2, HR_ALL_ORGANIZATION_UNITS_TL HRO_LE
WHERE HOI.ORG_INFORMATION_CONTEXT IN ('Legal Entity Accounting','Operating Unit Information')
AND HRO.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND TO_CHAR(SOB.SET_OF_BOOKS_ID(+)) = HOI.ORG_INFORMATION3
AND TO_CHAR(SOB2.SET_OF_BOOKS_ID(+)) = HOI.ORG_INFORMATION1
AND TO_CHAR(HRO_LE.ORGANIZATION_ID(+)) = HOI.ORG_INFORMATION2
--AND SUBSTR(HRO.NAME,1,2) IN ('HB')
ORDER BY 2,3

Read More

Summary Account Template definition review script

GL : Summary Account Template definition review script:-
--------------------------------------------------------------------

GL SUMMARY TEMPLATE DEFINITIONS

Small script showing summary template configuration across multiple books,

I have tested in 11.5.10.2 Instance. This will work in all the 11i Instances.

I have commented the SOB condition. Check your Instances SOB short-name and provide accordingly if you have multiple SOB in your business Structure.

select sob.name
, st.template_name
, st.concatenated_description
, st.account_category_code"Cat"
, st.START_ACTUALS_PERIOD_NAME "From"
, st.segment1_type||'-'||st.segment2_type||'-'||st.segment3_type||'-'||st.segment4_type||'-'||st.segment5_type||'-'||
st.segment6_type||'-'||st.segment7_type||'-'||st.segment8_type||'-'||st.segment9_type||'-'||st.segment10_type "Segment Type"
from GL_SUMMARY_TEMPLATES st, GL_SETS_OF_BOOKS sob
where st.SET_OF_BOOKS_ID = sob.SET_OF_BOOKS_ID
--and substr(sob.name,1,2) in ('HB')

Read More

Set of Books Configuration Overview

GL : Set of Books Configuration Overview:-
------------------------------------------------

SET OF BOOKS CONFIGURATION OVERVIEW

This SQL gives an overview of the set of book definitions and can be used when implementing multiple sets of books
to ensure consistent setup across countries and between environments.
Where clause can be added or commented out to just look at specific countries.

I have tested in 11.5.10.2 Instance. This will work in all the 11i Instances.

I have commented the SOB condition. Check your Instances SOB short-name and provide accordingly if you have multiple SOB in your business Structure.

SELECT SOB.SET_OF_BOOKS_ID "ID"
, SOB.NAME
, SOB.SHORT_NAME
, SOB.DESCRIPTION
, SOB.CHART_OF_ACCOUNTS_ID "COA ID"
, FST.ID_FLEX_STRUCTURE_CODE "CHART OF ACCOUNTS"
, SOB.CURRENCY_CODE "CURR"
, PT.USER_PERIOD_TYPE "PERIOD"
, SOB.PERIOD_SET_NAME
, SOB.FUTURE_ENTERABLE_PERIODS_LIMIT "FUT. PER"
, SOB.LATEST_OPENED_PERIOD_NAME "LATEST OPEN"
, SOB.ATTRIBUTE1"OPERATIONAL BOOK"
, SOB.ATTRIBUTE2"PPL ?"
, SOB.ENABLE_REVAL_SS_TRACK_FLAG||'.'||ENABLE_SECONDARY_TRACK_FLAG"SEC SEG TRACK?"
, RET.SEGMENT1||'-'||RET.SEGMENT2||'-'||RET.SEGMENT3||'-'||RET.SEGMENT4||'-'||RET.SEGMENT5||'-'||RET.SEGMENT6 "RETAINED EARNINGS"
, TRAN.SEGMENT1||'-'||TRAN.SEGMENT2||'-'||TRAN.SEGMENT3||'-'||TRAN.SEGMENT4||'-'||TRAN.SEGMENT5||'-'||TRAN.SEGMENT6 "TRAN EARNINGS"
, '---JOURNALS---'
, SOB.ALLOW_INTERCOMPANY_POST_FLAG"INTERCO?"
, SOB.ENABLE_JE_APPROVAL_FLAG"JRNL APP?"
, SOB.ENABLE_AUTOMATIC_TAX_FLAG"AUTO TAX?"
, SOB.SUSPENSE_ALLOWED_FLAG"SUSP?"
, SOB.TRACK_ROUNDING_IMBALANCE_FLAG"TRK RND?"
, '---AV BAL---'
, SOB.ENABLE_AVERAGE_BALANCES_FLAG||SOB.CONSOLIDATION_SOB_FLAG||SOB.TRANSACTION_CALENDAR_ID||SOB.NET_INCOME_CODE_COMBINATION_ID
||SOB.DAILY_TRANSLATION_RATE_TYPE||SOB.TRANSLATE_EOD_FLAG||SOB.TRANSLATE_QATD_FLAG||SOB.TRANSLATE_YATD_FLAG "NOT USED"
, '---BUDGET CNTL---'
, SOB.ENABLE_BUDGETARY_CONTROL_FLAG||SOB.REQUIRE_BUDGET_JOURNALS_FLAG||SOB.RES_ENCUMB_CODE_COMBINATION_ID "NOT USED"
, '---MRC---'
, SOB.MRC_SOB_TYPE_CODE "NOT USED"
FROM GL_SETS_OF_BOOKS SOB, FND_ID_FLEX_STRUCTURES FST, GL_CODE_COMBINATIONS TRAN, GL_CODE_COMBINATIONS RET, GL_PERIOD_TYPES PT
WHERE FST.ID_FLEX_NUM = SOB.CHART_OF_ACCOUNTS_ID
AND RET.CODE_COMBINATION_ID(+) = SOB.RET_EARN_CODE_COMBINATION_ID
AND TRAN.CODE_COMBINATION_ID(+) = SOB.CUM_TRANS_CODE_COMBINATION_ID
AND PT.PERIOD_TYPE = SOB.ACCOUNTED_PERIOD_TYPE
--AND SUBSTR(SOB.SHORT_NAME,1,2) IN (''HB')
ORDER BY 2

Read More

Segment Value Listing

GL : Segment Value Listing:-
--------------------------------

SEGMENT VALUE SET LISTINGS

Lists single or multiple segment value sets. This is used to perform a QA on chart of accounts values.

Examples of optional where clauses have also been provided below.

SELECT FFVS1.FLEX_VALUE_SET_NAME
--, FFVS1.FLEX_VALUE_SET_ID
, FFVAL1.FLEX_VALUE"VALUE"
, FFVAL1.SUMMARY_FLAG"PARENT ACC ?"
, FFVTL1.DESCRIPTION
, FFVAL1.ENABLED_FLAG
, FH.HIERARCHY_CODE
, SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),1,1)"BUDGET"
, SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),3,1)"POST"
, SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),5,1)"TYPE"
, SUBSTR(TO_CHAR(FFVAL1.COMPILED_Value_attributes),7,1)"Cntl"
, SUBSTR(TO_CHAR(ffval1.compiled_value_attributes),9,1)"Recon"
, ffval1.LAST_UPDATED_BY
, ffval1.LAST_UPDATE_DATE
FROM FND_FLEX_VALUES ffval1
, FND_FLEX_VALUES_TL ffvtl1
, FND_FLEX_VALUE_SETS ffvs1
, FND_ID_FLEX_SEGMENTS seg
, FND_FLEX_HIERARCHIES_VL fh
WHERE ffval1.FLEX_VALUE_SET_ID(+) = ffvs1.FLEX_VALUE_SET_ID
AND seg.FLEX_VALUE_SET_ID = ffvs1.FLEX_VALUE_SET_ID
AND seg.ID_FLEX_NUM = 51974 /* CoA ID is needed if segment is chart in multple CoA. Update for you configuration or remove if not applicable. */
AND ffval1.FLEX_VALUE_ID = ffvtl1.FLEX_VALUE_ID(+)
AND ffvs1.FLEX_VALUE_SET_NAME = 'Operations Account'
AND FFVAL1.STRUCTURED_HIERARCHY_LEVEL = FH.HIERARCHY_ID(+)
--AND SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),7,1) != 'N' -- NON-CONTROL ACCOUNTS ONLY
--AND SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),7,1) = 'Y' -- CONTROL ACCOUNTS ONLY
--AND FFVAL1.SUMMARY_FLAG = 'Y'
--AND FFVAL1.FLEX_VALUE >= '8000'
--AND FFVAL1.FLEX_VALUE <= '99999'
--AND FFVTL1.DESCRIPTION LIKE '%FTE%'
--AND FFVAL1.FLEX_VALUE LIKE '16%'
ORDER BY FFVS1.FLEX_VALUE_SET_NAME, FFVAL1.FLEX_VALUE

Read More

Mass Allocation Formula review script

GL : Mass Allocation Formula review script:-
---------------------------------------------------

Will show the definition of mass allocation batches and lines across multiple books in an easy to read format for review in excel
It has been written for a 10 segment CoA but can be modified to suit different structures.

I have tested in 11.5.10.2 Instance. This will work in all the 11i Instances.

select fst.ID_FLEX_STRUCTURE_NAME"Chart of Accounts"
, gab.VALIDATION_STATUS "Valid?"
, gab.name "Allocation Name"
, gaf.name "Formula Name"
, gaf.full_allocation_flag"Full?"
--, gaf.validation_status"Valid?"
, gafl.line_number"Line #"
, DECODE(gafl.line_number,1,'A',2,'B',3,'C',4,'T',5,'O','XXX')"Line"
, gafl.AMOUNT"Amount"
, gafl.currency_code "Curr"
, gafl.SEGMENT1||'-'||gafl.SEGMENT2||'-'||gafl.SEGMENT3||'-'||gafl.SEGMENT4||'-'||gafl.SEGMENT5||'-'||gafl.SEGMENT6
||'-'||gafl.SEGMENT7||'-'||gafl.SEGMENT8||'-'||gafl.SEGMENT9||'-'||gafl.SEGMENT10 "Account"
, gafl.segment_types_key "Segment"
, gafl.relative_period"Period"
--, gafl.transaction_currency"Curr"
, gafl.actual_flag"Actual?"
, gafl.amount_type"Amt Type"
from GL_ALLOC_BATCHES gab, GL_ALLOC_FORMULAS gaf, GL_ALLOC_FORMULA_LINES gafl
, FND_ID_FLEX_STRUCTURES_VL fst
where gab.allocation_batch_id = gaf.allocation_batch_id
and gab.CHART_OF_ACCOUNTS_ID = fst.id_flex_num
and gaf.allocation_formula_id = gafl.allocation_formula_id
--and substr(fst.ID_FLEX_STRUCTURE_CODE,1,2) in ('HB')
order by 1,3,4,6

Read More