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...
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...
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...
API: Customer API -Create a Party Site Use Using the above party site create a party site use
DECLARE
p_party_site_use_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_RE
C_TYPE;
x_party_site_use_id NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
BEGIN
p_party_site_use_rec.site_use_type := ’SHIP_TO’;
p_party_site_use_rec.party_site_id := 349327;
p_party_site_use_rec.created_by_module := ’TCA_EXAMPLE’;
hz_party_site_v2pub.create_party_site_use(
’T’,
p_party_site_use_rec,
x_party_site_use_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...
API: Customer API -Create a Party site
DECLARE
p_party_site_rec HZ_PARTY_SITE_V2PU
B.PARTY_SITE_REC_TYPE;
x_party_site_id NUMBER;
x_party_site_number VARCHAR2(2000);
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
BEGIN
p_party_site_rec.party_id := 1272023;
p_party_site_rec.location_id := 359086;
p_party_site_rec.identifying_address_flag := ’Y’;
p_party_site_rec.created_by_module := ’TCA_EXAMPLE’;
hz_party_site_v2pub.create_party_site(
’T’,
p_party_site_rec,
x_party_site_id,
x_party_site_number,
x_return_status,
x_msg_count,
x_msg_data);
dbms_output.put_line('party...
API: Customer API -Create a Location
DECLARE
p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
x_location_id NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
BEGIN
p_location_rec.country := ’US’;
p_location_rec.address1 := ’300 Oracle Parkway’;
p_location_rec.address2 := ’13th Floor’;
p_location_rec.city := ’Redwood Shores’;
p_location_rec.postal_code := ’94065’;
p_location_rec.state := ’CA’;
p_location_rec.created_by_module := ’TCA_EXAMPLE’;
hz_location_v2pub.create_location(
’T’,
p_location_rec,
x_location_id,
x_return_status,
x_msg_count,
x_msg_data);
dbms_output.put_line('location...
API: Customer API -Create an Organization
DECLARE
p_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_party_id NUMBER;
x_party_number VARCHAR2(2000);
x_profile_id NUMBER;
BEGIN
p_organization_rec.organization_name := ’ABC Corporation’;
p_organization_rec.created_by_module := ’TCA_EXAMPLE’;
hz_party_v2pub.create_organization (
’T’,
p_organization_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_party_id,
x_party_number,
x_profile_id);
dbms_output.put_line('party id '||x_party_id);
dbms_output.put_line(SubStr(’x_return_status...
Query : Invoices on hold
select distinct
ai.invoice_num,ai.invoice_amount,ai.invoice_date
,ai.invoice_received_date,ah.hold_lookup_code,ah.hold_reason,pv.vendor_name,pv.segment1 vendor_number
,ph.segment1 po_number
,gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6
--,aid.AMOUNT
from ap_invoices_all ai
,ap_holds_all ah
,po_vendors pv
,po_headers_all ph
,ap_invoice_distributions_all aid
,po_distributions_all pda
,gl_code_combinations gcc
where 1=1
and ai.invoice_id = ah.invoice_id
and ai.vendor_id = pv.vendor_id
--and...
Subscribe to:
Posts (Atom)
