PO权限控制查询

每个采购员只能查询自已的采购单

CREATE

OR

REPLACE

FUNCTION

CUX_PO_ACCESS(V_AGENT_ID

IN

NUMBER

,V_USER_ID

IN

NUMBER

)

RETURN

VARCHAR2

IS

l_exists

(

10

);

BEGIN

begin

select

'Y'

into

l_exists

from

dual

where

V_AGENT_ID

in

(

select

_id

from

per_all_people_f ppf,

per_all_assignments_f paf,

fnd_user fu

where

_id = _id

and

sysdate

between

greatest(ive_start_date,ive_start_date)

and

least(nvl(ive _end_date,

sysdate

+

< br>1

),nvl(ive_end_date,

sysda te

+

1

))

and

ee_id = _id

-- connect by _id = PRIOR

isor_id

-- update

根据上一层找下一层

(

主管去找对应的雇员

)

connect

by

isor_id =

PRIOR

_id

START

WITH

_id = V_USER_ID);

exception

when

others

then

l_exists :=

'N'

;

end

;

return

l_exists;

END

CUX_PO_ACCESS;

CREATE

OR

REPLACE

VIEW

PO_HEADERS_INQ_V

(row_id, po_release_flag, creation_date, acceptance_due_date,

amount_limit, approved_date, authorization_status,

blanket_total_amount,

closed_date,

comments,

end_date,

end_date_active,

firm_date,

government_context,

min_release_amount,

note_to_authorizer,

note_to_receiver, note_to_vendor, print_count, printed_date,

quote_vendor_quote_number, quote_warning_delay,

quote_warning_delay_unit, rate, rate_date, rate_type, reply_date,

revised_date, revision_num, rfq_close_date, start_date,

start_date_active, vendor_order_num, agent_id, bill_to_location_id,

from_header_id, po_header_id, ship_to_location_id, terms_id,

vendor_contact_id, vendor_id, vendor_site_id, closed_code,

currency_code, firm_status_lookup_code, fob_lookup_code,

freight_terms_lookup_code,

from_type_lookup_code,

quotation_class_code,

quote_type_lookup_code,

reply_method_lookup_code,

ship_via_lookup_code,

status_lookup_code, type_lookup_code, ussgl_transaction_code,

acceptance_required_flag, approval_required_flag, approved_flag,

cancel_flag, confirming_order_flag, enabled_flag, frozen_flag,

summary_flag, user_hold_flag, created_by, order_date, last_updated_by,

last_update_date, last_update_login, program_application_id,

program_id,

program_update_date,

request_id,

po_num,

segment2,

segment3,

segment4, segment5, attribute_category, attribute1, attribute2,

attribute3,

attribute4,

attribute5,

attribute6,

attribute7,

attribute8,

attribute9, attribute10, attribute11, attribute12, attribute13,

attribute14, attribute15, doc_type_name, can_preparer_approve_flag,

security_level_code,

vendor_name,

type_1099,

vat_code,

vendor_site_code,

address_line1,

address_line2,

address_line3,

city,

state,

zip,

country,

phone, fax, vendor_contact, terms_name, ship_to_location,

bill_to_location, rate_conversion_type, authorization_status_dsp,

fob_dsp,

freight_terms_dsp,

closed_code_dsp,

cancel_date,

cancel_reason,

cancelled_by,

hold_by,

hold_date,

hold_reason,

release_num,

release_type,

po_release_id, pcard_id, price_update_tolerance, pay_on_code,

pay_on_dsp, global_agreement_flag, enable_all_sites, owning_org_id,

org_id, cbc_accounting_date, consigned_consumption_flag,

shipping_control, shipping_control_dsp, conterms_exist_flag,

conterms_articles_upd_date, conterms_deliv_upd_date, change_summary,

pending_signature_flag, style_id)

AS

SELECT

,

'PO'

,

ON_DATE,

ANCE_DUE_DATE,

_LIMIT,

ED_DATE,

NVL

(IZATION_STATUS,

'INCOMPLETE'

),

T_TOTAL_AMOUNT,

_DATE,

TS,

_DATE,

_DATE_ACTIVE,

_DATE,

MENT_CONTEXT,

_RELEASE_AMOUNT,

_TO_AUTHORIZER,

_TO_RECEIVER,

_TO_VENDOR,

_COUNT,

D_DATE,

_VENDOR_QUOTE_NUMBER,

_WARNING_DELAY,

_WARNING_DELAY_UNIT,

,

_DATE,

_TYPE,

_DATE,

D_DATE,

ON_NUM,

_CLOSE_DATE,

_DATE,

_DATE_ACTIVE,

_ORDER_NUM,

_ID,

_TO_LOCATION_ID,

_HEADER_ID,

_HEADER_ID,

_TO_LOCATION_ID,

_ID,

_CONTACT_ID,

_ID,

_SITE_ID,

NVL

(_CODE,

'OPEN'

),

CY_CODE,

NVL

(_STATUS_LOOKUP_CODE,

_LOOKUP_CODE,

T_TERMS_LOOKUP_CODE,

_TYPE_LOOKUP_CODE,

),

'N'

ION_CLASS_CODE,

_TYPE_LOOKUP_CODE,

_METHOD_LOOKUP_CODE,

_VIA_LOOKUP_CODE,

_LOOKUP_CODE,

_LOOKUP_CODE,

_TRANSACTION_CODE,

ANCE_REQUIRED_FLAG,

AL_REQUIRED_FLAG,

ED_FLAG,

DECODE

(_FLAG,

'I'

,

NULL

, _FLAG),

MING_ORDER_FLAG,

D_FLAG,

NVL

(_FLAG,

'N'

),

Y_FLAG,

NVL

(_HOLD_FLAG,

'N'

),

D_BY,

ON_DATE,

_UPDATED_BY,

_UPDATE_DATE,

_UPDATE_LOGIN,

M_APPLICATION_ID,

M_ID,

M_UPDATE_DATE,

T_ID,

T1,

T2,

T3,

T4,

T5,

UTE_CATEGORY,

UTE1,

UTE2,

UTE3,

UTE4,

UTE5,

UTE6,

UTE7,

UTE8,

UTE9,

UTE10,

UTE11,

UTE12,

UTE13,

UTE14,

UTE15,

_NAME,

_PREPARER_APPROVE_FLAG,

TY_LEVEL_CODE,

_NAME,

_1099,

NULL

,

_SITE_CODE,

S_LINE1,

S_LINE2,

S_LINE3,

,

,

,

Y,

DECODE

(,

NULL

,

NULL

,

'('

|| _CODE ||

') '

||

),

DECODE

(,

NULL

,

NULL

,

'('

|| _AREA_CODE ||

') '

||

),

DECODE

(_NAME,

NULL

,

NULL

,

_NAME ||

', '

|| _NAME),

,

ON_CODE,

ON_CODE,

NULL

,

NULL

,

NULL

,

NULL

,

NULL

,

TO_DATE(

NULL

),

NULL

,

TO_NUMBER(

NULL

),

TO_NUMBER(

NULL

),

TO_DATE(

NULL

),

NULL

,

TO_NUMBER(

NULL

),

NULL

,

TO_NUMBER(

NULL

),

_ID,

_UPDATE_TOLERANCE,

_ON_CODE,