Expense Reports Approval Query

The query to show the employee approval limits are as follows:

SELECT b.full_name, a.cost_center, a.org_id, c.NAME org_name, a.signing_limit
FROM ap_web_signing_limits_all a,
per_all_people_f b,
hr_organization_units c
WHERE a.employee_id = b.person_id
AND a.org_id = c.organization_id
AND b.effective_start_date = (SELECT MAX (effective_start_date)
FROM apps.per_all_people_f
WHERE person_id = b.person_id)
AND a.document_type = ‘APEXP’

Navigation
Payables Manager -> Employees -> Signing Limits

Movement Statistics Report Shows Commodity Code As Blank

Log a service request to get assistance. The following SQL might help identify the cause of the issue.



STEPS
1. Navigate to Inventory > reports > transactions > run
2. Run movement statistics report (Standard report: Movement Statistics Report - INVSTMVT.rdf)
* Choose options including "Movement Type = Dispatch".
* Report Title:   Dispatch Movement Statistics Summary
3. Review output -- See commodity code column is blank

 
1) Confirm the category set id from Movement Parameter form:
select category_set_id
from mtl_stat_type_usages
where legal_entity_id = &legal_entity_id
and zone_code = 'EC'
and usage_type = 'INTERNAL'
and stat_type = 'INTRASTAT';

2) Find out category_id for the specific movement id

This is to find out if there is commodity code defined for the transaction item.
SELECT  sic.category_id
FROM  MTL_SYSTEM_ITEMS si  , MTL_ITEM_CATEGORIES sic
WHERE si.inventory_item_id = sic.inventory_item_id
AND si.organization_id = sic.organization_id
AND si.inventory_item_id = &inventory_item_id
AND si.organization_id = &organization_id
AND sic.category_set_id = &CategorysetidFromSQL1;

3) Find out commodity code :
SELECT substrb(mkv.concatenated_segments,1,230) , substrb(mic.description,1,230)
FROM MTL_CATEGORIES mic , MTL_CATEGORIES_KFV mkv
WHERE mic.category_id = mkv.category_id
AND mic.category_id = &CategoryIDFromSQL2;

R12 Expense Report Tables

All initial transactions will be in "ap_credit_card_trxns_all" before we submit the IE reports

​Table Name	​Description
​AP_CREDIT_CARD_TRXNS_ALL	​Table to store the corporate credit card transactions that are sent by the banks. These lines are saved as expense lines when the user creates the expense lines for credit cards
​AP_EXPENSE_REPORT_HEADERS_ALL	​Expense report header information
​AP_EXPENSE_REPORT_LINES_ALL	​Expense report lines information
​AP_EXP_REPORT_DISTS_ALL	​Expense report distribution information. It contains the accounts against each expense report line.