Query : AR to GL data transfer

SELECT gjjlv.period_name "Period" , gjb.name "Batch name" , gjjlv.header_name "Journal Entry For" , gjjlv.je_source "Source" , glcc.concatenated_segments "Accounts" , gjjlv.line_entered_dr "Entered Debit" , gjjlv.line_entered_cr "Entered Credit" , gjjlv.line_accounted_dr "Accounted Debit" , gjjlv.line_accounted_cr "Accounted Credit" , gjjlv.currency_code "Currency" , arm.name "Payment Method" , acra.receipt_number "Receipt Num" , acra.receipt_date "Receipt Date" , RA.CUSTOMER_NAME "Reference" , gjjlv.created_by "Gl Transfer By"
FROM apps.gl_je_journal_lines_v gjjlv ,
gl_je_lines gje ,
gl_je_headers gjh ,
gl_je_batches gjb ,
ar_cash_receipts_all acra ,
apps.ra_customers ra ,
apps.gl_code_combinations_kfv glcc ,
ar_receipt_methods arm
WHERE gjh.period_name IN ('OCT-2007','NOV-2007')
AND glcc.code_combination_id = gje.code_combination_id
AND gjh.JE_BATCH_ID = gjb.JE_BATCH_ID
AND gjh.JE_HEADER_ID = gje.JE_HEADER_ID
AND gjh.period_name = gjb.default_period_name
AND gjh.period_name = gje.period_name
AND gjjlv.period_name = gjh.period_name
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.LINE_JE_LINE_NUM = gje.je_line_num
AND gjjlv.line_code_combination_id = glcc.code_combination_id
AND gjjlv.line_reference_4 = acra.receipt_number
AND ra.customer_id = acra.pay_from_customer
AND acra.receipt_method_id = arm.receipt_method_id
AND gjjlv.SUBLEDGER_DOC_SEQUENCE_VALUE = acra.DOC_SEQUENCE_VALUE
AND gjjlv.SUBLEDGER_DOC_SEQUENCE_id = acra.DOC_SEQUENCE_ID


Filter with Segment1 or other segments

Read More

Query : to find the process in Workflow using a known function name or Activity name

select
wat1.display_name "process name",wat.display_name "activity name",wa.function "function"
from wf_process_activities wpa
,wf_activities_tl wat
,wf_activities_tl wat1
,wf_activities wa
where wpa.activity_item_type = wa.item_type
and wpa.instance_label = wa.name
and wat.item_type = wa.item_type
and wat.version = wa.version
and wat.language = 'US'
and wat.name = wa.name
and wat1.name = wpa.process_name
and wat1.item_type = wat.item_type
and wat1.version = wat.version
and wat1.language = wat.language
and wat1.version = wpa.process_version
and (upper(wa.function) like '%'||upper('&func_name')||'%' or (wa.FUNCTION IS NULL AND '&func_name'='%'))
and (upper(wat.display_name) like '%'||upper('&act_name')||'%' or (wat.display_name IS NULL AND '&act_name'='%'))

Using this query we can find the workflow process name where the given function name or activity is being called.

Read More

Query : Query to find receipts against a PO shipment line

execute fnd_client_info.set_org_context('org_id');

SELECT
pol.po_header_id,
pol.po_line_id,
pll.line_location_id,
pll.quantity,
rsh. shipment_header_id,
rsh. receipt_source_code,
rsh. vendor_id,
rsh. vendor_site_id,
rsh. organization_id,
rsh. shipment_num,
rsh. receipt_num,
rsh. ship_to_location_id,
rsh. bill_of_lading,
rsl.shipment_line_id,
rsl.QUANTITY_SHIPPED,
rsl.QUANTITY_RECEIVED ,
rct.transaction_type,
rct.transaction_id,
decode(pol.order_type_lookup_code,'RATE',nvl(rct.amount,0),'FIXED PRICE',nvl(rct.amount,0),
nvl(rct.source_doc_quantity,0) ) transaction_qty
from rcv_transactions rct
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_lines pol
, po_line_locations pll
where rct.po_line_location_id = 28302 --- check it out
and rct.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and nvl(pol.order_type_lookup_code,'QUANTITY') NOT IN ('RATE','FIXED PRICE')
and rct.shipment_line_id=rsl.shipment_line_id
and rsl.shipment_header_id=rsh.shipment_header_id
order by rct.transaction_id
/

Read More

R12 - how does iExpenses join to ap_checks_all

R12 does have the ap_expense_report_headers_all.vouchno column. You can join the ap_expense_report_headers_all.vouchno with ap_invoices_all.invoice_id.

Alternatively, you can also use the following join, ap_invoices_all.PRODUCT_TABLE = 'AP_EXPENSE_REPORT_HEADERS_ALL' AND ap_invoices_all.REFERENCE_KEY1 = ap_expense_report_headers_all.report_header_id.

However, would suggested to  use the invoice_id and vouchno join.

Read More

INFO: Project Accounting main tables

Here are the below are important Oracle Project Accounting tables

• PA_PROJECTS_ALL
• Information about projects
• PA_AGREEMENTS_ALL
• Customer contracts that serve as the basis for work authorization
• PA_BILL_RATES_ALL
• Information about bill rates and markups of standard bill rate schedules
• PA_BILLING_ASSIGNMENTS_ALL
• Assignments of billing extensions to a project type, project, or task
• PA_COST_DISTRIBUTION_LINES_ALL
• Information about expenditure item cost distribution
• PA_CUST_REV_DIST_LINES_ALL
• Information about expenditure item revenue distribution
• PA_DRAFT_INVOICE_DETAILS_ALL
• Intercompany invoice details for cross charged transactions
• PA_DRAFT_INVOICES_ALL
• Information about draft invoices generated for projects
• PA_DRAFT_REVENUES_ALL
• Information about draft revenue generated for projects
• PA_EXPENDITURE_COST_RATES_ALL
• Cost rates for non-labor expenditure types
• PA_EXPENDITURE_GROUPS_ALL
• Groups of pre-approved expenditures
• PA_EXPENDITURE_ITEMS_ALL
• The smallest units of expenditure charged to projects and tasks
• PA_EXPENDITURES_ALL
• Groups of expenditure items incurred by employees or organizations for an expenditure period
• PA_PERIODS_ALL
• Implementation-defined periods against which project performance is measured
• PA_PROJECT_ASSET_LINES_ALL
• Summarized project CIP costs
• PA_PROJECT_ASSETS_ALL
• Assets defined for capital projects
• PA_EXPENDITURE_TYPES
• Implementation-defined classifications of expenditures charged to projects and tasks
• PA_EXPENDITURE_CATEGORIES
• Implementation-defined groupings of expenditure types by type of cost
• PA_CLASS_CATEGORIES
• Implementation-defined categories for classifying projects
• PA_CLASS_CODES
• Implementation-defined values within class categories that can be used to classify projects
• PA_PROJECT_CLASSES
• Class codes of class categories that are used to classify projects
• PA_BUDGET_VERSIONS
• Versions of project budgets
• PA_BUDGET_LINES
• Detail lines of project and task budgets

Read More

API: Customer API -Create a Customer Account

DECLARE
p_cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
p_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
p_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE REC_TYPE;
x_cust_account_id NUMBER;
x_account_number VARCHAR2(2000);
x_party_id NUMBER;
x_party_number VARCHAR2(2000);
x_profile_id NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
BEGIN
p_cust_account_rec.account_name := ’John’’s A/c’;
p_cust_account_rec.created_by_module := ’TCA_EXAMPLE’;
p_person_rec.person_first_name := ’John’;
p_person_rec.person_last_name := ’Smith’;
hz_cust_account_v2pub.create_cust_account( ’T’, p_cust_account_rec, p_person_rec,
p_customer_profile_rec, ’F’, x_cust_account_id, x_account_number, x_party_id,
x_party_number,
x_profile_id,
x_return_status,
x_msg_count,
x_msg_data);
dbms_output.put_line(SubStr(’x_return_status = ’||x_return_status,
1,255));
dbms_output.put_line(’x_msg_count = ’||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr(’x_msg_data = ’||x_msg_data,1,255));
IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||’. ’||SubStr(FND_MSG_PUB.Get(p_encoded =>
FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END;

Read More

API: Customer API -Create a Contact Point ( of type Phone)

DECLARE
p_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
p_edi_rec HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE;
p_email_rec HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE;
p_phone_rec HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;
p_telex_rec HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE;
p_web_rec HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE;
x_return_status VARCHAR2(2000);
x_msg_coun t NUMBER;
x_msg_data VARCHAR2(2000);
x_contact_point_id NUMBER;
BEGIN
p_contact_point_rec.contact_point_type := ’PHONE’;
p_contact_point_rec.owner_table_name := ’HZ_PARTIES’;
p_contact_point_rec.owner_table_id := ’1272023’;
p_contact_point_rec.primary_flag := ’Y’;
p_contact_point_rec.contact_point_purpose := ’BUSINESS’;
p_phone_rec.phone_area_code := ’650’;
p_phone_rec.phone_country_code := ’1’;
p_phone_rec.phone_number := ’506-7000’;
p_phone_rec.phone_line_type := ’GEN’;
p_contact_point_rec.created_by_module := ’TCA_EXAMPLE’;
hz_contact_point_v2pub.create_contact_point( ’T’, p_contact_point_rec,
p_edi_rec, p_email_rec,
p_phone_rec,
p_telex_rec,
p_web_rec,
x_contact_point_id,
x_return_status,
x_msg_count,
x_msg_data);
dbms_output.put_line(SubStr(’x_return_status = ’||x_return_status,1,255));
dbms_output.put_line(’x_msg_count = ’||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr(’x_msg_data = ’||x_msg_data,1,255));
IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||’. ’||SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END;

Read More