資源描述:
《oracleerp的sql腳本》由會(huì)員上傳分享,免費(fèi)在線(xiàn)閱讀,更多相關(guān)內(nèi)容在工程資料-天天文庫(kù)。
1、/**************************/oracleERP的SQL腳本/**************************/庫(kù)存貨位列表.sqlselectinventory_location_id,organization_id,description,subinventory_code,segment1inventory_location_codefromMTL_ITEM_LOCATIONSorderbysubinventory_code,inventory_location_code;庫(kù)存會(huì)計(jì)期間.sqlSELECTSTATUS,PERIOD_NAME,PERIO
2、D_NUMBER,PERIOD_YEAR,START_DATE,END_DATE,CLOSE_DATE,REC_TYPE,ORGANIZATION_IDFROMORG_ACCT_PERIODS_VWHERE((rec_type='ORG_PERIOD'))orderbyEND_DATEdesc;庫(kù)存組織列.sqlselecta.organization_id,a.nameFROMhr_all_organization_unitsawherea.organization_id>82;事物處理活動(dòng)列表.sqlselect*frommfg_lookupswherelookup_type='MT
3、L_TRANSACTION_ACTION'ORDERBYLOOKUP_CODE;事物處理來(lái)源類(lèi)型列表.sqlSELECTTRANSACTION_SOURCE_TYPE_NAME,DESCRIPTION,VALIDATED_FLAG,TRANSACTION_SOURCE_TYPE_ID,USER_DEFINED_FLAG,DISABLE_DATEFROMMTL_TXN_SOURCE_TYPESWHEREUSER_DEFINED_FLAG='N'orderbytransaction_source_type_name;接收事物處理.sqlSELECTt.po_header_id,t.po_li
4、ne_id,t.po_release_id,SUM(DECODE(t.transaction_type,'DELIVER',t.quantity,'RETURNTORECEIVING',DECODE(t.destination_type_code,'INVENTORY',-t.quantity,'EXPENSE',-t.quantity,0),'CORRECT',DECODE(t.destination_type_code,'INVENTORY',t.quantity,'EXPENSE',t.quantity,0),0))deliver_qtya,SUM(t.po_unit_price*
5、t.currency_conversion_rate*DECODE(t.transaction_type,'DELIVER',t.quantity,'RETURNTORECEIVING',DECODE(t.destination_type_code,'INVENTORY',-t.quantity,'EXPENSE',-t.quantity,0),'CORRECT',DECODE(t.destination_type_code,'INVENTORY',t.quantity,'EXPENSE',t.quantity,0),0))deliver_amount,SUM(DECODE(t.tran
6、saction_type,'RECEIVE',t.quantity,'RETURNTOVENDOR',-t.quantity,'CORRECT',DECODE(t.destination_type_code,'RECEIVING',t.quantity,0),0))receive_qtya,SUM(t.po_unit_price*t.currency_conversion_rate*DECODE(t.transaction_type,'RECEIVE',t.quantity,'RETURNTOVENDOR',-t.quantity,'CORRECT',DECODE(t.destinati
7、on_type_code,'RECEIVING',t.quantity,0),0))receive_amount,t.unit_of_measure,rsl.item_id,rsl.item_revision,rsl.item_description,g.period_year,g.period_number,rsl.from_organization_idFROMpo.rcv_transactionst,apps.org_acct