OracleCOST

2015年11月19日星期四

groovy:NQY_R_Text

//groovy:NQY_R_Text
import groovy.sql.Sql
import java.sql.Timestamp
def sqlOracle = Sql.newInstance( 'jdbc:oracle:thin:@192.168.170.2:1521:HEALTH', 'IDEMPIERE','IDEMPIERE', 'oracle.jdbc.OracleDriver' )
def m_created = new Timestamp(System.currentTimeMillis());
def dataList = []
def f0,f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20
def v0,v1,v2,v3,v4,v5,v6,v7,v8,v9,v10,v11,v12,v13,v14,v15,v16,v17,v18,v19,v20
def i0 = 0
def fileName = ''
def dir = new File("C:\\PAP")
dir.eachFileRecurse (FileType.FILES) { file ->
// list << file
// println file.path
// String result = names.substring(0, names.indexOf('-'))
   if ((file.path).indexOf('新泰宜') > 0 &&
       (file.path).indexOf('子抹電子檔') > 0 )
      println file.path
      fileName = file.path

}
sql.eachRow('select * from PROJECT') { row ->
     println "${row.name.padRight(10)} ($row.url)"
}

// def fileName = 'C:/PAP/NQY_1.CSV'
def file = new File(fileName).getText('UTF-8')
def lineCount = 0
file.eachLine() { line ->
    def field = line.tokenize(",")
    lineCount++
    // println "line: ${lineCount} , ${field[0]}, ${field[1]}, ${field[2]}) "
    f0=null
    f1=null
    f2=null
    f3=null
    f4=null
    if (field[0] != null) f0 = field[0].trim()
    if (field[1] != null) f1 = field[1].trim()
    if (field[2] != null) f2 = field[2].trim()
    if (field[3] != null) f3 = field[3].trim()
    if (field[4] != null) f4 = field[4].trim()
    if (f0!=null && (v0=="序號" || i0>0) ) {
        i0 = f0.isInteger() ? (f0 as int) : null
        if (i0!=null) {
           println "line: ${i0} "
           sqlOracle.execute('insert into NQY_PAP(序號, 細胞病理編號, 個案姓名, 身份證字號, 出生日期, 採檢日期, 收到日期, 確診日期, 收到天數, 檢驗天數, 抹片品質, 尚可或難以判讀原因,可能感染, 診斷結果, 支付方式)values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',[f0,f1,f2,f3,f4 ,field[5],field[6],field[7],field[8],field[9],field[10],field[11],field[12],field[13],field[14]])
        }
     }
     v0=f0;v1=f1;
}
result="

groovy:NQY_R_Directory

//groovy:NQY_R_Directory
import groovy.io.FileType
import groovy.sql.Sql
import java.sql.Timestamp
import java.util.Calendar;
import java.util.Date;
def sql = Sql.newInstance('jdbc:oracle:thin:@192.168.170.2:1521:HEALTH','IDEMPIERE',,'IDEMPIERE','oracle.jdbc.driver.OracleDriver')

println(Calendar.getInstance().get(Calendar.HOUR_OF_DAY))   // 17
println(Calendar.getInstance().get(Calendar.MINUTE))              // 12
println(Calendar.getInstance().get(Calendar.SECOND))          // 23

def filepath = ''
def list = []
def dir = new File("C:\\PAP")

dir.eachFileRecurse (FileType.FILES) { file ->
// println file.path
// String result = names.substring(0, names.indexOf('-'))
     if ((file.path).indexOf('新泰宜') > 0 &&
       (file.path).indexOf('子抹電子檔') > 0 ){
          list << file
          // println file.path
       }
}
println "矩陣讀出...."
list.each {
   println it.path
   def fileName = it.path
   def v_c1 = 0
   sql.eachRow("select count(*) as c1 from nqy_pap_imp_log where filename='"+fileName+"'" ) { row -> // 是否已寫入過
   println "($row.c1)"
   }
   def file = new File(fileName).getText('Big5')
   def lineCount = 0
   file.eachLine() { line ->
      def field = line.tokenize(",")
       lineCount++
       println "line: ${lineCount} , ${field[0]}, ${field[1]}, ${field[2]}) "
   }
}

/*
// CREATE TABLE nqy_pap_imp_log (filename VARCHAR2(200),created DATE);

list.each {
   println it.path
   def fileName = it.path
   def file = new File(fileName).getText('Big5')
// def file = new File(fileName).getText('UTF-8')
   def lineCount = 0
   file.eachLine() { line ->
      def field = line.tokenize(",")
      lineCount++
   // println "line: ${lineCount} , ${field[0]}, ${field[1]}, ${field[2]}) "
      f0=null
      f1=null
      f2=null
      f3=null
      f4=null
      if (field[0] != null) f0 = field[0].trim()
      if (field[1] != null) f1 = field[1].trim()
      if (field[2] != null) f2 = field[2].trim()
      if (field[3] != null) f3 = field[3].trim()
      if (field[4] != null) f4 = field[4].trim()
      if (f0!=null && (v0=="序號" || i0>0) ) {
          i0 = f0.isInteger() ? (f0 as int) : null
          if (i0!=null) {
              println "line: ${i0} "
          }
          v0=f0;v1=f1;
      }
   }
}*/
return ""

NQY_UPDATE_PAP_GROOVY

import groovy.sql.Sql
import java.sql.Timestamp
def sql = Sql.newInstance('jdbc:oracle:thin:@192.168.170.2:1521:HEALTH','IDEMPIERE',,'IDEMPIERE','oracle.jdbc.driver.OracleDriver')
sql.eachRow('SELECT x.序號,x.細胞病理編號,x.個案姓名,x.身份證字號,x.出生日期 ,SUBSTR(x.採檢日期,1,3)||SUBSTR(x.採檢日期,5,2)||SUBSTR(x.採檢日期,8,2) AS 採檢日期, x.收到日期,x.確診日期,x.收到天數,x.檢驗天數,x.抹片品質,x.尚可或難以判讀原因,x.可能感染,x.診斷結果,x.支付方式,y.REGNO '
     +'FROM IDEMPIERE.NQY_PAP x '
     +'INNER JOIN IPD.FTH008K y ON TRIM(x.身份證字號)=y.IDNO ') { r1 ->
    println "[有檢驗報告:] ${r1.細胞病理編號.padRight(10)} ${r1.個案姓名} ${r1.REGNO} ${r1.採檢日期}"
   def p1= r1.REGNO
   def p2= r1.採檢日期
   sql.eachRow('SELECT DISTINCT b.ORDER_NO, b.CASENO, C.REGNO, b.SDATE, B.OBDATE, b.CODE '
       +'FROM IPD.FOB003K b '
       +'INNER JOIN IPD.FOB002K c ON C.CASENO=B.CASENO '
       +'WHERE 1=1 '
       +'AND c.REGNO =? '
       +'AND b.OBDATE=? '
       +'AND (SUBSTR(b.CODE,1,4)= ? OR b.CODE=? OR b.CODE=? ) '
       +'AND b.CANCEL_MARK IS NULL '
       +'ORDER BY c.REGNO ', [ p1, p2, 'PAPS', '031', '31' ] ) { r2 ->
       def v_result = ''
       if         (r1.診斷結果.indexOf('Category 0:') > 0) v_result = '0';
       else if (r1.診斷結果.indexOf('Category 1:') > 0) v_result = '1';
       else if (r1.診斷結果.indexOf('Category 2:') > 0) v_result = '2';
       else if (r1.診斷結果.indexOf('Category 3:') > 0) v_result = '3';
       else if (r1.診斷結果.indexOf('Category 4:') > 0) v_result = '4';
       else if (r1.診斷結果.indexOf('Category 5:') > 0) v_result = '5';
       //println "${r2.ORDER_NO.padRight(10)} ${r2.ORDER_NO} ${r2.CASENO} ${r2.CODE} ${v_result}"
   }
}
return "成功"

NQY_UPDATE_PAP

CREATE OR REPLACE PROCEDURE IDEMPIERE.NQY_UPDATE_PAP(P_REGNO VARCHAR2,P_SDATE_B VARCHAR2, P_SDATE_E VARCHAR2)
AS
-- 乳攝檢查檔更新鑑驗檔 MAMO 值
-- EXECUTE IDEMPIERE.NQY_UPDATE_PAP ('00073449','1040826','1040826');
--   COMMIT
-- select * from all_tab_cols where column_name='HOSRT' and length(table_name)=7
v_key           varchar2(80);
v_result        varchar2(20);
v_date          varchar2(20);
BEGIN
dbms_output.put_line('--------------------');
--v_search_string := P_REGNO;
-- INNER JOIN FOB003K c ON b.ORDER_NO=a.ORDER_NO -- b.CASENO
-- DELETE IDEMPIERE.NQY_PAP COMMIT
-- SELECT * FROM IDEMPIERE.NQY_PAP

FOR a1 IN (
             SELECT
x.序號         ,-- VARCHAR2(40),
x.細胞病理編號 ,-- VARCHAR2(40),
x.個案姓名     ,-- VARCHAR2(40),
x.身份證字號   ,-- VARCHAR2(40),
x.出生日期     ,-- VARCHAR2(40),
SUBSTR(x.採檢日期,1,3)||SUBSTR(x.採檢日期,5,2)||SUBSTR(x.採檢日期,8,2) AS 採檢日期,-- VARCHAR2(40),
x.收到日期     ,-- VARCHAR2(40),
x.確診日期     ,-- VARCHAR2(40),
x.收到天數     ,-- VARCHAR2(40),
x.檢驗天數     ,-- VARCHAR2(40),
x.抹片品質     ,-- VARCHAR2(40),
x.尚可或難以判讀原因 ,--VARCHAR2(40),
x.可能感染     ,--VARCHAR2(40),
x.診斷結果     ,--VARCHAR2(40),
x.支付方式     ,--VARCHAR2(40)
y.REGNO
              FROM IDEMPIERE.NQY_PAP x --M200161967
        INNER JOIN IPD.FTH008K       y ON TRIM(x.身份證字號)=y.IDNO
)LOOP

     dbms_output.put_line(a1.REGNO||' : '||a1.採檢日期||'xxx');

     FOR a2 IN (
              SELECT DISTINCT b.ORDER_NO,
                      b.CASENO, C.REGNO, b.SDATE, B.OBDATE, b.CODE --b.CODE_TYPE,
               FROM IPD.FOB003K b
         INNER JOIN IPD.FOB002K c ON C.CASENO=B.CASENO
              WHERE 1=1
                AND c.REGNO = a1.REGNO
                AND b.OBDATE= a1.採檢日期
                AND (SUBSTR(b.CODE,1,4)='PAPS' OR b.CODE='031' OR b.CODE='31')
                AND b.CANCEL_MARK IS NULL
             --   AND EXISTS(SELECT 1
             --                FROM IPD.FEX002K d
             --               WHERE d.CLASS = 'PAP'
             --                 AND(d.RESULT IS NULL OR d.RESULT='') --- 還有空白的才做
             --                 AND d.PKEY_CASENO=b.CASENO )
           ORDER BY c.REGNO
     )LOOP

       dbms_output.put_line(a1.REGNO||' : '||a1.採檢日期||' : '||a2.ORDER_NO||' : '||a2.CASENO||' : '||a2.REGNO||' : '||a2.CODE);
       v_result := '';
       IF    INSTR(a1.診斷結果,'Category 0:') > 0 THEN
             v_result := '0';
       ELSIF INSTR(a1.診斷結果,'Category 1:') > 0 THEN
             v_result := '1';
       ELSIF INSTR(a1.診斷結果,'Category 2:') > 0 THEN
             v_result := '2';
       ELSIF INSTR(a1.診斷結果,'Category 3:') > 0 THEN
             v_result := '3';
       ELSIF INSTR(a1.診斷結果,'Category 4:') > 0 THEN
             v_result := '4';
       ELSIF INSTR(a1.診斷結果,'Category 5:') > 0 THEN
             v_result := '5';
       END IF;

    --   COMMIT
       v_date := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR( ADD_MONTHS(SYSDATE,-120)    ,'YYYYMMDD'),'99999999') -19110000,'9999999'));
       IF LENGTH(v_date) = 5 THEN
          v_date := '00'||v_date;
       ELSIF LENGTH(v_date) = 6 THEN
          v_date := '0'||v_date;
       END IF;
       /*
       UPDATE FEX002K d
          SET d.RESULT = v_result,
              MEDICAL_DIVISION = 'L006',
              REPORT_DATE = v_date,
              REPORT_TIME = NULL,
              REPORT_USRID= 'L006',
              CONFIRM_FLAG = 'Y',
              CONFIRM_DATE = v_date,
              CONFIRM_TIME = NULL,
              CONFIRM_USRID= 'L006'
        WHERE d.CLASS = 'MAMO'
          AND(d.RESULT IS NULL OR d.RESULT = '')
          AND d.PKEY_CASENO=a1.CASENO;
      */


     END LOOP;
END LOOP;
END;
/

2015年11月17日星期二

醫院系統轉檔_20151117_02

// def fileName = 'C:/PAP/NQY_1.CSV'

def file = new File(fileName).getText('UTF-8')

def lineCount = 0

file.eachLine() { line ->

def field = line.tokenize(",")

lineCount++

// println "line: ${lineCount} , ${field[0]}, ${field[1]}, ${field[2]}) "

f0=null

f1=null

f2=null

f3=null

f4=null

if (field[0] != null) f0 = field[0].trim()

if (field[1] != null) f1 = field[1].trim()

if (field[2] != null) f2 = field[2].trim()

if (field[3] != null) f3 = field[3].trim()

if (field[4] != null) f4 = field[4].trim()

if (f0!=null && (v0=="序號" || i0>0) ) {

i0 = f0.isInteger() ? (f0 as int) : null

if (i0!=null) {

println "line: ${i0} "

sqlOracle.execute('insert into NQY_PAP(序號, 細胞病理編號, 個案姓名, 身份證字號, 出生日期, 採檢日期, 收到日期, 確診日期, 收到天數, 檢驗天數, 抹片品質, 尚可或難以判讀原因,可能感染, 診斷結果, 支付方式)values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',[f0,f1,f2,f3,f4 ,field[5],field[6],field[7],field[8],field[9],field[10],field[11],field[12],field[13],field[14]])

}

v0=f0;v1=f1;

}

result=""

醫院系統轉檔 20151117_01

醫院系統轉檔

//groovy:NQY_R_Directory
import groovy.io.FileType
import groovy.sql.Sql
import java.sql.Timestamp
import java.util.Calendar;
import java.util.Date;
def sql = Sql.newInstance('jdbc:oracle:thin:@192.168.170.2:1521:HEALTH','IDEMPIERE',,'IDEMPIERE','oracle.jdbc.driver.OracleDriver')

println(Calendar.getInstance().get(Calendar.HOUR_OF_DAY)) // 17
println(Calendar.getInstance().get(Calendar.MINUTE)) // 12
println(Calendar.getInstance().get(Calendar.SECOND)) // 23

def filepath = ''
def list = []
def dir = new File("C:\\PAP")

dir.eachFileRecurse (FileType.FILES) { file ->
// println file.path
// String result = names.substring(0, names.indexOf('-'))
if ((file.path).indexOf('新泰宜') > 0 &&
(file.path).indexOf('子抹電子檔') > 0 ){
list << file
// println file.path
}
}
println "矩陣讀出...."
list.each {
println it.path
def fileName = it.path
def v_c1 = 0
sql.eachRow("select count(*) as c1 from nqy_pap_imp_log where filename='"+fileName+"'" ) { row -> // 是否已寫入過
println "($row.c1)"
}
def file = new File(fileName).getText('Big5')
def lineCount = 0
file.eachLine() { line ->
def field = line.tokenize(",")
lineCount++
println "line: ${lineCount} , ${field[0]}, ${field[1]}, ${field[2]}) "
}
}

/*
// CREATE TABLE nqy_pap_imp_log (filename VARCHAR2(200),created DATE);

list.each {
println it.path
def fileName = it.path
def file = new File(fileName).getText('Big5')
// def file = new File(fileName).getText('UTF-8')
def lineCount = 0
file.eachLine() { line ->
def field = line.tokenize(",")
lineCount++
// println "line: ${lineCount} , ${field[0]}, ${field[1]}, ${field[2]}) "
f0=null
f1=null
f2=null
f3=null
f4=null
if (field[0] != null) f0 = field[0].trim()
if (field[1] != null) f1 = field[1].trim()
if (field[2] != null) f2 = field[2].trim()
if (field[3] != null) f3 = field[3].trim()
if (field[4] != null) f4 = field[4].trim()
if (f0!=null && (v0=="序號" || i0>0) ) {
i0 = f0.isInteger() ? (f0 as int) : null
if (i0!=null) {
println "line: ${i0} "
}
v0=f0;v1=f1;
}
}
}*/
return ""

2015年9月21日星期一

1.1. Modeling the Enterprise

We can host multiple Oracle eBusiness suites on one Instance separated by security group.

This is primarily aimed for Application service providers who host and maintain Oracle Apps for many SME clients on pay-per-use basis.

1.1.1. Business Group

The business group represents the highest level in the organization structure. It secures human resources information.

If you have multiple business groups, you must associate each responsibility to one and only one business group.

You associate a business group with a responsibility via the HR:Business Group system profile option.

Fig1.1.1 Organization Structure

1.1.2. Set of Books

A financial reporting entity that uses a particular chart of accounts, functional currency, and accounting calendar.

Oracle General Ledger secures transaction information (such as journal entries and balances)by set of books.

1.1.3. Legal Entity

Set the responsibility from which you define the organizations in the profile option HR:

User Type to HR User, to get access to the Define Organizations window. Log in to the responsibility associated with the business group for which you are defining an organization.

Define a legal entity. It is then automatically attached to the correct business group. Legal entity is a legal company for which you prepare fiscal or tax reports.

You assign tax identifiers and other legal entity information to this type of organization.

1.1.4. Operating Unit

An operating unit is associated with a legal entity. Information is secured by operating unit for applications viz:

Order Management, Shipping Execution, Oracle Payables, Oracle Purchasing, Oracle Receivables and Oracle Cash Management.

To run any of these applications, you choose a responsibility associated with an operating unit.

1.1.5. Inventory Organization

Inventory Organization is an organization for which you track inventory transactions and balances, and/or an organization that manufactures or distributes products viz:manufacturing plants, warehouses, distribution centers, and sales offices.

The following applications secure information by inventory organization: Oracle Inventory, Bills of Material, Engineering, Work in Process,Master Scheduling/MRP, Capacity, and Purchasing receiving functions.

Define Organization :

Fig1.1.2 Organization definition of Boston Manufacturing

Navigate to Define Organization window. Enter a name for the organization.You can define all types of organizations viz manufacturing facility, warehouse, branch office etc. Choose a location where this organization exists.Enter what type(s) of organization is this, in organization classification viz:

GRE/Legal Entity : For a Legal Entity, click others and enter Accounting info like Set of Books.

Operating Unit : For an Operating Unit, enter Accounting Information like Legal Entity, Set of Books.

Fig1.1.3 Organization information of Boston Manufacturing Inv Organization

Inventory Organization : For an Inv Organization enter Accounting Information like Set of Books, Legal Entity & Operating Unit.
HR Organization : HR organizations represent the basic work structure of any enterprise. They usually represent the functional management, or reporting groups that exist within a business group. Click others and enter all such information .

1.1.5. Organization Architecture

	HR_ALL_ORGANIZATION_UNITS ORGANIZATION_ID NAME BUSINESS_GROUP_ID LOCATION_ID DATE_FROM INTERNAL_EXTERNAL_FLAG INTERNAL_ADDRESS_LINE TYPE
	HR_ORGANIZATION_INFORMATION ORG_INFORMATION_ID ORGANIZATION_ID ORG_INFORMATION_CONTEXT ORG_INFORMATION1 ~ 20

All organizations are maintained in HR_ALL_ORGANIZATION_UNITS table and organization classifications like Legal entity or operating unit information are stored in HR_ORGANIZATION_INFORMATION table. There are 3 views provided based on these 2 tables to easily find out the Legal entity, Operating unit and Inventory organization viz:

HR_LEGAL_ENTITIES

ORGANIZATION_ID                BUSINESS_GROUP_ID
NAME
DATE_FROM
DATE_TO
SET_OF_BOOKS_ID
VAT_REGISTRATION_NUMBER

HR_OPERATING_UNITS

ORGANIZATION_ID        BUSINESS_GROUP_ID
NAME
DATE_FROM
DATE_TO
LEGAL_ENTITY_ID
SET_OF_BOOKS_ID

ORG_ORGANIZATION_DEFINITIONS

ORGANIZATION_ID
BUSINESS_GROUP_ID
USER_DEFINITION_ENABLE_DATE
DISABLE_DATE
ORGANIZATION_CODE
ORGANIZATION_NAME
SET_OF_BOOKS_ID
CHART_OF_ACCOUNTS_ID
INVENTORY_ENABLED_FLAG
OPERATING_UNIT
LEGAL_ENTITY

ALTER TABLE HR.HR_ALL_ORGANIZATION_UNITS
DROP PRIMARY KEY CASCADE;

DROP TABLE HR.HR_ALL_ORGANIZATION_UNITS CASCADE CONSTRAINTS;

CREATE TABLE HR.HR_ALL_ORGANIZATION_UNITS
(
ORGANIZATION_ID             NUMBER(15)        NOT NULL,
BUSINESS_GROUP_ID           NUMBER(15)        NOT NULL,
COST_ALLOCATION_KEYFLEX_ID NUMBER(9),
LOCATION_ID                 NUMBER(15),
SOFT_CODING_KEYFLEX_ID      NUMBER(15),
DATE_FROM                   DATE              NOT NULL,
NAME                        VARCHAR2(240 BYTE) NOT NULL,
COMMENTS                    LONG,
DATE_TO                     DATE,
INTERNAL_EXTERNAL_FLAG      VARCHAR2(30 BYTE),
INTERNAL_ADDRESS_LINE       VARCHAR2(80 BYTE),
TYPE                        VARCHAR2(30 BYTE),
REQUEST_ID                  NUMBER(15),
PROGRAM_APPLICATION_ID      NUMBER(15),
PROGRAM_ID                  NUMBER(15),
PROGRAM_UPDATE_DATE         DATE,
ATTRIBUTE_CATEGORY          VARCHAR2(30 BYTE),
ATTRIBUTE1                  VARCHAR2(150 BYTE),
ATTRIBUTE2                  VARCHAR2(150 BYTE),
ATTRIBUTE3                  VARCHAR2(150 BYTE),
ATTRIBUTE4                  VARCHAR2(150 BYTE),
ATTRIBUTE5                  VARCHAR2(150 BYTE),
ATTRIBUTE6                  VARCHAR2(150 BYTE),
ATTRIBUTE7                  VARCHAR2(150 BYTE),
ATTRIBUTE8                  VARCHAR2(150 BYTE),
ATTRIBUTE9                  VARCHAR2(150 BYTE),
ATTRIBUTE10                 VARCHAR2(150 BYTE),
ATTRIBUTE11                 VARCHAR2(150 BYTE),
ATTRIBUTE12                 VARCHAR2(150 BYTE),
ATTRIBUTE13                 VARCHAR2(150 BYTE),
ATTRIBUTE14                 VARCHAR2(150 BYTE),
ATTRIBUTE15                 VARCHAR2(150 BYTE),
ATTRIBUTE16                 VARCHAR2(150 BYTE),
ATTRIBUTE17                 VARCHAR2(150 BYTE),
ATTRIBUTE18                 VARCHAR2(150 BYTE),
ATTRIBUTE19                 VARCHAR2(150 BYTE),
ATTRIBUTE20                 VARCHAR2(150 BYTE),
LAST_UPDATE_DATE            DATE,
LAST_UPDATED_BY             NUMBER(15),
LAST_UPDATE_LOGIN           NUMBER(15),
CREATED_BY                  NUMBER(15),
CREATION_DATE               DATE,
OBJECT_VERSION_NUMBER       NUMBER(9),
PARTY_ID                    NUMBER(15),
ATTRIBUTE21                 VARCHAR2(150 BYTE),
ATTRIBUTE22                 VARCHAR2(150 BYTE),
ATTRIBUTE23                 VARCHAR2(150 BYTE),
ATTRIBUTE24                 VARCHAR2(150 BYTE),
ATTRIBUTE25                 VARCHAR2(150 BYTE),
ATTRIBUTE26                 VARCHAR2(150 BYTE),
ATTRIBUTE27                 VARCHAR2(150 BYTE),
ATTRIBUTE28                 VARCHAR2(150 BYTE),
ATTRIBUTE29                 VARCHAR2(150 BYTE),
ATTRIBUTE30                 VARCHAR2(150 BYTE)
)

CREATE INDEX HR.HR_ORGANIZATION_UNITS_FK1 ON HR.HR_ALL_ORGANIZATION_UNITS (BUSINESS_GROUP_ID)
CREATE INDEX HR.HR_ORGANIZATION_UNITS_FK2 ON HR.HR_ALL_ORGANIZATION_UNITS (COST_ALLOCATION_KEYFLEX_ID)
CREATE INDEX HR.HR_ORGANIZATION_UNITS_FK3 ON HR.HR_ALL_ORGANIZATION_UNITS (LOCATION_ID)
CREATE INDEX HR.HR_ORGANIZATION_UNITS_FK4 ON HR.HR_ALL_ORGANIZATION_UNITS (SOFT_CODING_KEYFLEX_ID)
CREATE UNIQUE INDEX HR.HR_ORGANIZATION_UNITS_PK ON HR.HR_ALL_ORGANIZATION_UNITS (ORGANIZATION_ID)
CREATE UNIQUE INDEX HR.HR_ORGANIZATION_UNITS_UK2 ON HR.HR_ALL_ORGANIZATION_UNITS (NAME, BUSINESS_GROUP_ID)
------------------------------------------------------------
CREATE OR REPLACE TRIGGER APPS.HR_ALL_ORGANIZATION_UNITS_OVN
BEFORE INSERT OR UPDATE ON HR_ALL_ORGANIZATION_UNITS
FOR EACH ROW
BEGIN
if hr_general.g_data_migrator_mode <> 'Y' then
IF NOT hr_oru_shd.return_api_dml_status THEN
   IF INSERTING THEN
     :NEW.object_version_number := 1;
   ELSE
     :NEW.object_version_number := :OLD.object_version_number + 1;
   END IF;
END IF;
end if;
END HR_ALL_ORGANIZATION_UNITS_OVN;
/

CREATE OR REPLACE TRIGGER APPS.HR_ALL_ORGANIZATION_UNITS_WHO
before insert or update on HR_ALL_ORGANIZATION_UNITS
for each row
declare
l_sysdate DATE := sysdate;
begin
-- Trigger generated by hrcretrg.sql at 2011/08/12 18:01:23.
if hr_general.g_data_migrator_mode <> 'Y' then
if inserting and
      :new.created_by    is null and
      :new.creation_date is null then
    :new.created_by      := fnd_global.user_id;
    :new.creation_date   := l_sysdate;
   end if;
   if :new.last_update_date is null
      or :new.last_update_date = nvl(:old.last_update_date,
                                     hr_general.start_of_time)
      or :new.last_update_date = trunc(:new.last_update_date)
   then
      :new.last_update_date := l_sysdate;
end if;
:new.last_updated_by   := fnd_global.user_id;
:new.last_update_login := fnd_global.login_id;
end if;
end;
/

CREATE OR REPLACE SYNONYM APPS.HR_ALL_ORGANIZATION_UNITS FOR HR.HR_ALL_ORGANIZATION_UNITS;

ALTER TABLE HR.HR_ALL_ORGANIZATION_UNITS ADD (
CONSTRAINT HR_OU_INTERNAL_EXTERNAL_FL_CHK
CHECK (
INTERNAL_EXTERNAL_FLAG IN ('INT', 'EXT')

)
ENABLE VALIDATE,
CONSTRAINT HR_ORGANIZATION_UNITS_PK
PRIMARY KEY
(ORGANIZATION_ID)
USING INDEX HR.HR_ORGANIZATION_UNITS_PK
ENABLE VALIDATE,
CONSTRAINT HR_ORGANIZATION_UNITS_UK2
UNIQUE (NAME, BUSINESS_GROUP_ID)
USING INDEX HR.HR_ORGANIZATION_UNITS_UK2
ENABLE VALIDATE);

ALTER TABLE HR.HR_ALL_ORGANIZATION_UNITS ADD (
CONSTRAINT HR_ORGANIZATION_UNITS_FK1 FOREIGN KEY (BUSINESS_GROUP_ID)
REFERENCES HR.HR_ALL_ORGANIZATION_UNITS (ORGANIZATION_ID)
ENABLE VALIDATE,

CONSTRAINT HR_ORGANIZATION_UNITS_FK2 FOREIGN KEY (COST_ALLOCATION_KEYFLEX_ID)
REFERENCES HR.PAY_COST_ALLOCATION_KEYFLEX (COST_ALLOCATION_KEYFLEX_ID)
ENABLE VALIDATE,

CONSTRAINT HR_ORGANIZATION_UNITS_FK3 FOREIGN KEY (LOCATION_ID)
REFERENCES HR.HR_LOCATIONS_ALL (LOCATION_ID)
ENABLE VALIDATE,

CONSTRAINT HR_ORGANIZATION_UNITS_FK4 FOREIGN KEY (SOFT_CODING_KEYFLEX_ID)
REFERENCES HR.HR_ALL_ORGANIZATION_UNITS (ORGANIZATION_ID)
ENABLE VALIDATE);

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON HR.HR_ALL_ORGANIZATION_UNITS TO APPS WITH GRANT OPTION;

------------------------------------------
ALTER TABLE HR.HR_ORGANIZATION_INFORMATION DROP PRIMARY KEY CASCADE;

DROP TABLE HR.HR_ORGANIZATION_INFORMATION CASCADE CONSTRAINTS;

CREATE TABLE HR.HR_ORGANIZATION_INFORMATION
(
ORG_INFORMATION_ID       NUMBER               NOT NULL,
ORG_INFORMATION_CONTEXT VARCHAR2(40 BYTE)    NOT NULL,
ORGANIZATION_ID          NUMBER(15)           NOT NULL,
ORG_INFORMATION1         VARCHAR2(150 BYTE),
ORG_INFORMATION10        VARCHAR2(150 BYTE),
ORG_INFORMATION11        VARCHAR2(150 BYTE),
ORG_INFORMATION12        VARCHAR2(150 BYTE),
ORG_INFORMATION13        VARCHAR2(150 BYTE),
ORG_INFORMATION14        VARCHAR2(150 BYTE),
ORG_INFORMATION15        VARCHAR2(150 BYTE),
ORG_INFORMATION16        VARCHAR2(150 BYTE),
ORG_INFORMATION17        VARCHAR2(150 BYTE),
ORG_INFORMATION18        VARCHAR2(150 BYTE),
ORG_INFORMATION19        VARCHAR2(150 BYTE),
ORG_INFORMATION2         VARCHAR2(150 BYTE),
ORG_INFORMATION20        VARCHAR2(150 BYTE),
ORG_INFORMATION3         VARCHAR2(150 BYTE),
ORG_INFORMATION4         VARCHAR2(150 BYTE),
ORG_INFORMATION5         VARCHAR2(150 BYTE),
ORG_INFORMATION6         VARCHAR2(150 BYTE),
ORG_INFORMATION7         VARCHAR2(150 BYTE),
ORG_INFORMATION8         VARCHAR2(150 BYTE),
ORG_INFORMATION9         VARCHAR2(150 BYTE),
REQUEST_ID               NUMBER(15),
PROGRAM_APPLICATION_ID   NUMBER(15),
PROGRAM_ID               NUMBER(15),
PROGRAM_UPDATE_DATE      DATE,
ATTRIBUTE_CATEGORY       VARCHAR2(30 BYTE),
ATTRIBUTE1               VARCHAR2(150 BYTE),
ATTRIBUTE2               VARCHAR2(150 BYTE),
ATTRIBUTE3               VARCHAR2(150 BYTE),
ATTRIBUTE4               VARCHAR2(150 BYTE),
ATTRIBUTE5               VARCHAR2(150 BYTE),
ATTRIBUTE6               VARCHAR2(150 BYTE),
ATTRIBUTE7               VARCHAR2(150 BYTE),
ATTRIBUTE8               VARCHAR2(150 BYTE),
ATTRIBUTE9               VARCHAR2(150 BYTE),
ATTRIBUTE10              VARCHAR2(150 BYTE),
ATTRIBUTE11              VARCHAR2(150 BYTE),
ATTRIBUTE12              VARCHAR2(150 BYTE),
ATTRIBUTE13              VARCHAR2(150 BYTE),
ATTRIBUTE14              VARCHAR2(150 BYTE),
ATTRIBUTE15              VARCHAR2(150 BYTE),
ATTRIBUTE16              VARCHAR2(150 BYTE),
ATTRIBUTE17              VARCHAR2(150 BYTE),
ATTRIBUTE18              VARCHAR2(150 BYTE),
ATTRIBUTE19              VARCHAR2(150 BYTE),
ATTRIBUTE20              VARCHAR2(150 BYTE),
LAST_UPDATE_DATE         DATE,
LAST_UPDATED_BY          NUMBER(15),
LAST_UPDATE_LOGIN        NUMBER(15),
CREATED_BY               NUMBER(15),
CREATION_DATE            DATE,
OBJECT_VERSION_NUMBER    NUMBER(9),
PARTY_ID                 NUMBER(15)
)
TABLESPACE HRD
RESULT_CACHE (MODE DEFAULT)
PCTUSED    40
PCTFREE    10
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          16K
            NEXT             256K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        4
            FREELIST GROUPS 4
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE INDEX HR.HR_ORGANIZATION_INFORMATIO_FK1 ON HR.HR_ORGANIZATION_INFORMATION
(ORG_INFORMATION_CONTEXT)
LOGGING
TABLESPACE HRX
PCTFREE    0
INITRANS   11
MAXTRANS   255
STORAGE    (
            INITIAL          16K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        4
            FREELIST GROUPS 4
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX HR.HR_ORGANIZATION_INFORMATIO_FK2 ON HR.HR_ORGANIZATION_INFORMATION
(ORGANIZATION_ID, ORG_INFORMATION_CONTEXT)
LOGGING
TABLESPACE HRX
PCTFREE    0
INITRANS   11
MAXTRANS   255
STORAGE    (
            INITIAL          16K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        4
            FREELIST GROUPS 4
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX HR.HR_ORGANIZATION_INFORMATIO_IX1 ON HR.HR_ORGANIZATION_INFORMATION
(ORG_INFORMATION1)
LOGGING
TABLESPACE HRX
PCTFREE    0
INITRANS   11
MAXTRANS   255
STORAGE    (
            INITIAL          16K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        4
            FREELIST GROUPS 4
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX HR.HR_ORGANIZATION_INFORMATIO_PK ON HR.HR_ORGANIZATION_INFORMATION
(ORG_INFORMATION_ID)
LOGGING
TABLESPACE HRX
PCTFREE    0
INITRANS   11
MAXTRANS   255
STORAGE    (
            INITIAL          16K
            NEXT             256K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        4
            FREELIST GROUPS 4
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE OR REPLACE TRIGGER APPS.HR_ORGANIZATION_INFORMATIO_OVN
BEFORE INSERT OR UPDATE ON HR_ORGANIZATION_INFORMATION
FOR EACH ROW
BEGIN
if hr_general.g_data_migrator_mode <> 'Y' then
IF NOT hr_ori_shd.return_api_dml_status THEN
   IF INSERTING THEN
     :NEW.object_version_number := 1;
   ELSE
     :NEW.object_version_number := :OLD.object_version_number + 1;
   END IF;
END IF;

end if;
END HR_ORGANIZATION_INFORMATIO_OVN;
/

CREATE OR REPLACE TRIGGER APPS.HR_ORGANIZATION_INFORMATIO_WHO
before insert or update on HR_ORGANIZATION_INFORMATION
for each row
declare
l_sysdate DATE := sysdate;
begin
-- Trigger generated by hrcretrg.sql at 2011/08/12 07:33:31.
if hr_general.g_data_migrator_mode <> 'Y' then
if inserting and
      :new.created_by    is null and
      :new.creation_date is null then
    :new.created_by      := fnd_global.user_id;
    :new.creation_date   := l_sysdate;
   end if;
   if :new.last_update_date is null
      or :new.last_update_date = nvl(:old.last_update_date,
                                     hr_general.start_of_time)
      or :new.last_update_date = trunc(:new.last_update_date)
   then
      :new.last_update_date := l_sysdate;
end if;
:new.last_updated_by   := fnd_global.user_id;
:new.last_update_login := fnd_global.login_id;
end if;
end;
/

CREATE OR REPLACE TRIGGER APPS.hr_org_info_ari AFTER INSERT
/*
-- Copyright (C) 1992 Oracle Corporation UK Ltd., Chertsey, England.
-- All rights reserved.
-- Name : hr_org_info_ari :Trigger will insert Business Group details when
--        Org is defined as a business group.
-- 70.0 02-DEC-92 SZWILLIA Date Created
-- 70.1 03-DEC-92 SZWILLIA Failed again
-- 70.2 12-JAN-93 SZWILLIA Corrected WHEN statement.
-- 70.3 20-JAN-93 SZWILLIA Corrected error handling
-- 70.4 12-MAR-93 PKATTWOOD Added exit to end of file
-- 70.5 20-MAY-93 AMCGHEE   Removed alot of comments (RDBMS bug)
-- 70.10 26-APR-95 JTHURING context = 'Business Group Information'; was:
--                context='CLASS' and org_info1='HR_BG'
-- 110.1 20-aug-97 KHABIBUL removed drop trigger command and changed
--                create to create or replace. This was done
--                to avoid the ORA- error which would have been
--                raised by the drop command if the trigger
--                didn't exist (Part of Clean up process for R11).
-- 115.0 25-Jan-99 sxshah HRMS Data migrator changes.
-- 115.1 05-Jun-00 ccarter Added org_information6 parameter to
--                          insert_bus_grp_details in order to be
--                          able to create a Job Group each time a
--                          Business Group is created.
-- 115.2 25-jan-02 mbocutt added dbdrv and checkfile
-- 115.9 06-aug-02 vbanner changed checkfile to nocheck so file always runs
*/
ON HR_ORGANIZATION_INFORMATION FOR EACH ROW
WHEN (
new.org_information_context = 'Business Group Information'
      )
begin
if hr_general.g_data_migrator_mode <> 'Y' then
hr_organization.insert_bus_grp_details(:new.organization_id
                                        ,:new.org_information9
                        ,:new.org_information6
                                        ,:new.last_update_date
                                        ,:new.last_updated_by
                                        ,:new.last_update_login
                                        ,:new.created_by
                                        ,:new.creation_date);
end if;
end hr_org_info_ari;
/

CREATE OR REPLACE TRIGGER APPS.hr_org_info_bri BEFORE INSERT
ON HR_ORGANIZATION_INFORMATION
FOR EACH ROW
WHEN (
new.org_information_context = 'Business Group Information'
      )
DECLARE

CURSOR c_sg_enabled
IS
SELECT 'Y'
FROM fnd_profile_options fpo
      ,fnd_profile_option_values pov
WHERE fpo.profile_option_name = 'ENABLE_SECURITY_GROUPS'
   AND fpo.profile_option_id = pov.profile_option_id
   AND fpo.application_id = pov.application_id
   AND pov.level_id = 10002
   AND pov.profile_option_value = 'Y'
   AND to_number(pov.level_value) BETWEEN 800 AND 900;
--
CURSOR c_sec_grp_name_curs IS
SELECT substrb(security_group_name, 1, 80)
FROM fnd_security_groups_tl;
--
CURSOR c_sec_grp_name_length (p_owner VARCHAR2) IS
SELECT data_length
FROM all_tab_columns
WHERE column_name='SECURITY_GROUP_NAME'
   AND table_name='FND_SECURITY_GROUPS_TL'
   AND owner = p_owner;
--
l_security_group_name hr_all_organization_units.name%TYPE;
l_exists               VARCHAR2(1) DEFAULT NULL;
l_sg_enabled           BOOLEAN      DEFAULT FALSE;
l_sg_name              VARCHAR2(80);
l_bg_name              VARCHAR2(80);
l_sec_length           NUMBER;
--
-- Variables for GET_APP_INFO
--
l_out_status           VARCHAR2(30);
l_out_industry         VARCHAR2(30);
l_owner                VARCHAR2(30);
l_value                BOOLEAN;
--
-- End of Variables for GET_APP_INFO
--

begin

--
-- Find the schema for FND
--
l_value := FND_INSTALLATION.GET_APP_INFO ('FND', l_out_status,
                                          l_out_industry, l_owner);

   --
   -- Create a security group for the new business group, and populate the
   -- org_information14 column with the id
   --

   IF hr_general.g_data_migrator_mode <> 'Y' THEN
      --
      -- If security groups are enabled then create one for the
      -- business group that is about to be inserted.
      --
      OPEN c_sg_enabled;
      --
      FETCH c_sg_enabled INTO l_exists;
      --
      IF c_sg_enabled%FOUND THEN
         l_sg_enabled := TRUE;
      ELSE
         l_sg_enabled := FALSE;
      END IF;
      --
      CLOSE c_sg_enabled;
      --
      IF l_sg_enabled THEN
         SELECT hou.name
           INTO l_security_group_name
           FROM hr_all_organization_units hou
          WHERE hou.organization_id = :new.organization_id;
         --
         -- Create the security group
         --
         -- If the setup business group is being created then do not
         -- create a security group - just use the standard SG
         --
         IF :new.organization_id = 0 THEN
           :new.org_information14 := 0;
         ELSE
           --
           OPEN c_sec_grp_name_length(l_owner);
           --
           FETCH c_sec_grp_name_length INTO l_sec_length;
           --
           CLOSE c_sec_grp_name_length;
           --
           IF l_sec_length = 80 THEN
              --
              -- If length of fnd_security_groups_tl.security_group_name = 80
              -- (therefore has not been expanded to 240) then truncate
              -- business group name to 80.
              --
              OPEN c_sec_grp_name_curs;
              --
              FETCH c_sec_grp_name_curs INTO l_sg_name;
              --
              IF substrb(l_security_group_name, 1, 80) = l_sg_name THEN
                 --
                 -- If first 80 chars of existing sg name match first 80 chars
                 -- of new sg name then raise error.
                 --
                 CLOSE c_sec_grp_name_curs;
                 --
                 hr_utility.set_message(800, 'PER_289704_80CHAR_MATCH_SG');
                 hr_utility.raise_error;
                 --
              ELSE
                 --
                 CLOSE c_sec_grp_name_curs;
                 --
                 l_bg_name := substrb(l_security_group_name, 1, 80);
                 --
                 :new.org_information14 :=
                 fnd_security_groups_api.Create_Group
                    (to_char(:new.organization_id) -- security group key
                    ,l_bg_name                     -- business group name
                    ,' '                           -- description
                    );
              END IF;
            END IF;
          END IF;
   ELSE
      -- if security groups are not enabled then default the
      -- org_information14 column to 0.
      :new.org_information14 := 0;
   END IF;
END IF;
END hr_org_info_bri;
/

CREATE OR REPLACE TRIGGER APPS.HR_PA_MAINTN_ORG_HIST_BRD
BEFORE DELETE
ON HR_ORGANIZATION_INFORMATION
FOR EACH ROW
DECLARE
v_return_status      VARCHAR2(2000);
v_error_message_code VARCHAR2(2000);
l_exists             VARCHAR2(1) := 'N';
l_pa_class           VARCHAR2(1) := 'N';
BEGIN

       v_return_status := FND_API.G_RET_STS_SUCCESS;

       -- Bug 2917985 - Added check for use of PJR/Utilization
       -- Add an additional OR to check whether resource's organization
       -- already in pa_resources_denorm.
       BEGIN
       SELECT 'Y'
       INTO   l_exists
       FROM   dual
       WHERE exists (SELECT 'Y'
                      FROM   pa_resources_denorm
                      WHERE resource_organization_id = :old.organization_id
                      AND    rownum = 1);
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
             l_exists := 'N';
       END;

       /*Commenting below for bug 7118880*/
--       IF (l_exists = 'Y' OR
--           PA_INSTALL.is_prm_licensed = 'Y' OR
--           PA_INSTALL.is_utilization_implemented = 'Y') THEN

       IF (l_exists = 'Y') THEN -- changed IF condn for Bug 7118880

          -- if the organization is of type Expenditure type then
          -- call the work flow to update pa objects

          -- Add a check to see if the organization has a classification
          -- of PA Exp Org, since in R12, the OU can be entered for
          -- HR orgs also. Only want to update if the info context is
          -- Exp Organization Defaults and the class of PA Exp Org
          -- exists, even if the update is happening via the HR class.

          -- Fixed bug 4669716 - Have to comment out this check
          -- because of mutation errors. Cannot select from the
          -- same table that is being updated.

          /*
          BEGIN
          SELECT 'Y'
          INTO   l_pa_class
          FROM   hr_organization_information
          WHERE organization_id = :new.organization_id
          AND    org_information1 = 'PA_EXPENDITURE_ORG'
          AND    org_information_context = 'CLASS'
          AND    rownum = 1;

          EXCEPTION WHEN NO_DATA_FOUND THEN
             l_pa_class := 'N';
          END;
          */

          l_pa_class := 'Y';

          If (:old.org_information_context = 'Exp Organization Defaults' AND
              l_pa_class = 'Y') OR
             :old.org_information_context = 'Project Resource Job Group' Then

            pa_hr_update_pa_entities.update_project_entities(
                p_calling_mode       => 'DELETE'
               ,p_table_name         => 'HR_ORGANIZATION_INFORMATION'
               ,p_org_id_new         => :old.organization_id
               ,p_org_info1_new      => NULL
               ,p_org_info1_old      => :old.org_information1
               ,p_org_info_context   => :old.org_information_context
               ,x_return_status      => v_return_status
               ,x_error_message_code => v_error_message_code);

          end if;
       END IF;
Exception
    When OTHERS then
          raise;
END;
/

CREATE OR REPLACE TRIGGER APPS.HR_PA_MAINTN_ORG_HIST_BRI
-- $Header: pahrorg.sql 120.3.12010000.2 2008/08/22 16:20:48 mumohan ship $
BEFORE INSERT
ON HR_ORGANIZATION_INFORMATION
FOR EACH ROW
DECLARE
v_return_status      VARCHAR2(2000);
v_error_message_code VARCHAR2(2000);
l_pa_class           VARCHAR2(1) := 'N';
BEGIN

       v_return_status := FND_API.G_RET_STS_SUCCESS;

       -- Bug 2917985 - Added check for use of PJR/Utilization

       IF (PA_INSTALL.is_prm_licensed = 'Y' OR
           PA_INSTALL.is_utilization_implemented = 'Y') THEN

          -- if the organization is of type Expenditure type
          -- or Project Resource Job Group then
          -- call the work flow to update pa objects

          -- Add a check to see if the organization has a classification
          -- of PA Exp Org, since in R12, the OU can be entered for
          -- HR orgs also. Only want to update if the info context is
          -- Exp Organization Defaults and the class of PA Exp Org
          -- exists, even if the update is happening via the HR class.

-- Commenting this check for bug 5410918: To avoid mutation errors.
-- This was done for update and delete triggers earlier.
/*
          BEGIN
          SELECT 'Y'
          INTO   l_pa_class
          FROM   hr_organization_information
          WHERE organization_id = :new.organization_id
          AND    org_information1 = 'PA_EXPENDITURE_ORG'
          AND    org_information_context = 'CLASS'
          AND    rownum = 1;

          EXCEPTION WHEN NO_DATA_FOUND THEN
             l_pa_class := 'N';
          END;
*/
          l_pa_class := 'Y'; -- Added for bug 5410918

          If (:new.org_information_context = 'Exp Organization Defaults' AND
              l_pa_class = 'Y') OR
             (:new.org_information_context = 'Project Resource Job Group') then

             pa_hr_update_pa_entities.update_project_entities(
                 p_calling_mode       => 'INSERT'
                ,p_table_name         => 'HR_ORGANIZATION_INFORMATION'
                ,p_org_id_new         => :new.organization_id
                ,p_org_info1_new      => :new.org_information1
                ,p_org_info1_old      => null
                ,p_org_info_context   => :new.org_information_context
                ,x_return_status      => v_return_status
                ,x_error_message_code => v_error_message_code);

          end if;
       END IF;

Exception
    When OTHERS then
          raise;
END;
/

CREATE OR REPLACE TRIGGER APPS.HR_PA_MAINTN_ORG_HIST_BRU
BEFORE UPDATE OF
ORG_INFORMATION1
ON HR_ORGANIZATION_INFORMATION
FOR EACH ROW
DECLARE
v_return_status      VARCHAR2(2000);
v_error_message_code VARCHAR2(2000);
v_msg_data           VARCHAR2(2000);
v_msg_count          NUMBER;
l_exists             VARCHAR2(1) := 'N';
l_pa_class           VARCHAR2(1) := 'N';
BEGIN

       v_return_status := FND_API.G_RET_STS_SUCCESS;

       -- Bug 2917985 - Added check for use of PJR/Utilization
       -- Add an additional OR to check whether resource's organization
       -- already in pa_resources_denorm.
       BEGIN
       SELECT 'Y'
       INTO   l_exists
       FROM   dual
       WHERE exists (SELECT 'Y'
                      FROM   pa_resources_denorm
                      WHERE resource_organization_id = :new.organization_id
                      AND    rownum = 1);
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
             l_exists := 'N';
       END;

       IF (l_exists = 'Y' OR
           PA_INSTALL.is_prm_licensed = 'Y' OR
           PA_INSTALL.is_utilization_implemented = 'Y') THEN

          -- if the organization is of type Expenditure type then
          -- call the work flow to update pa objects

          -- Add a check to see if the organization has a classification
          -- of PA Exp Org, since in R12, the OU can be entered for
          -- HR orgs also. Only want to update if the info context is
          -- Exp Organization Defaults and the class of PA Exp Org
          -- exists, even if the update is happening via the HR class.

          -- Fixed bug 4669716 - Have to comment out this check
          -- because of mutation errors. Cannot select from the
          -- same table that is being updated.

          /*
          BEGIN
          SELECT 'Y'
          INTO   l_pa_class
          FROM   hr_organization_information
          WHERE organization_id = :new.organization_id
          AND    org_information1 = 'PA_EXPENDITURE_ORG'
          AND    org_information_context = 'CLASS'
          AND    rownum = 1;

          EXCEPTION WHEN NO_DATA_FOUND THEN
             l_pa_class := 'N';
          END;
          */

          l_pa_class := 'Y';

          If ((:new.org_information_context = 'Exp Organization Defaults' OR
               :old.org_information_context = 'Exp Organization Defaults') AND
               l_pa_class = 'Y') OR
             :new.org_information_context = 'Project Resource Job Group' OR
             :old.org_information_context = 'Project Resource Job Group' Then

             pa_hr_update_pa_entities.update_project_entities(
                 p_calling_mode       => 'UPDATE'
                ,p_table_name         => 'HR_ORGANIZATION_INFORMATION'
                ,p_org_id_new         => :new.organization_id
                ,p_org_info1_new      => :new.org_information1
                ,p_org_info1_old      => :old.org_information1
                ,p_org_info_context   => :new.org_information_context
                ,x_return_status      => v_return_status
                ,x_error_message_code => v_error_message_code);

         end if;
      END IF;

Exception
    When OTHERS then
          raise;
END;
/

CREATE OR REPLACE TRIGGER APPS.HR_PA_MAIN_ORG_INFO_HIST_BRI
-- $Header: perpaorg.sql 115.0 2001/02/21 02:46:50 pkm ship        $
BEFORE INSERT OR UPDATE
OF org_information2
ON HR_ORGANIZATION_INFORMATION
FOR EACH ROW
DECLARE
v_err_code number;
v_err_stage varchar2(300);
v_err_stack varchar2(300);
BEGIN

   IF (:new.org_information_context = 'CLASS'
     and   (:new.org_information1 = 'PA_PROJECT_ORG'
           or   :new.org_information1 = 'PA_EXPENDITURE_ORG')) Then
   IF (pa_imp.pa_implemented_all) THEN

      pa_org_utils.maintain_org_info_hist_bri (    :new.organization_id,
                       :new.org_information1,
                       :new.org_information_context,
                       :new.org_information2,
                       v_err_code,
                       v_err_stage,
                       v_err_stack);
    if v_err_code < 0 then
      fnd_message.set_name('PA', 'PA_ALL_ORACLE_ERROR');
      fnd_message.set_token('errno', to_char(v_err_code));
      fnd_message.set_token('stage', v_err_stage);
      app_exception.raise_exception;
    elsif v_err_code > 0 then
      fnd_message.set_name('PA', v_err_stage);
      app_exception.raise_exception;
    end if;

END IF;
end if;
END;
/

CREATE OR REPLACE SYNONYM APPS.HR_ORGANIZATION_INFORMATION FOR HR.HR_ORGANIZATION_INFORMATION;

CREATE OR REPLACE PUBLIC SYNONYM HR_ORGANIZATION_INFORMATION FOR HR.HR_ORGANIZATION_INFORMATION;

ALTER TABLE HR.HR_ORGANIZATION_INFORMATION ADD (
CONSTRAINT HR_ORGANIZATION_INFORMATIO_PK
PRIMARY KEY
(ORG_INFORMATION_ID)
USING INDEX HR.HR_ORGANIZATION_INFORMATIO_PK
ENABLE VALIDATE);

ALTER TABLE HR.HR_ORGANIZATION_INFORMATION ADD (
CONSTRAINT HR_ORGANIZATION_INFORMATIO_FK1
FOREIGN KEY (ORG_INFORMATION_CONTEXT)
REFERENCES HR.HR_ORG_INFORMATION_TYPES (ORG_INFORMATION_TYPE)
ENABLE VALIDATE,
CONSTRAINT HR_ORGANIZATION_INFORMATIO_FK2
FOREIGN KEY (ORGANIZATION_ID)
REFERENCES HR.HR_ALL_ORGANIZATION_UNITS (ORGANIZATION_ID)
ON DELETE CASCADE
ENABLE VALIDATE);

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON HR.HR_ORGANIZATION_INFORMATION TO APPS WITH GRANT OPTION;

GRANT SELECT ON HR.HR_ORGANIZATION_INFORMATION TO HR_REPORTING_USER;

DROP VIEW APPS.ORG_ORGANIZATION_DEFINITIONS;

/* Formatted on 2015/9/21 下午 10:17:33 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.ORG_ORGANIZATION_DEFINITIONS
(
   ORGANIZATION_ID,
   BUSINESS_GROUP_ID,
   USER_DEFINITION_ENABLE_DATE,
   DISABLE_DATE,
   ORGANIZATION_CODE,
   ORGANIZATION_NAME,
   SET_OF_BOOKS_ID,
   CHART_OF_ACCOUNTS_ID,
   INVENTORY_ENABLED_FLAG,
   OPERATING_UNIT,
   LEGAL_ENTITY
)
AS
   SELECT HOU.ORGANIZATION_ID ORGANIZATION_ID,
          HOU.BUSINESS_GROUP_ID,
          HOU.DATE_FROM USER_DEFINITION_ENABLE_DATE,
          HOU.DATE_TO DISABLE_DATE,
          MP.ORGANIZATION_CODE ORGANIZATION_CODE,
          HOU.NAME ORGANIZATION_NAME,
          LGR.LEDGER_ID SET_OF_BOOKS_ID,
          LGR.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID,
          HOI1.ORG_INFORMATION2 INVENTORY_ENABLED_FLAG,
          DECODE (
             HOI2.ORG_INFORMATION_CONTEXT,
             'Accounting Information', TO_NUMBER (HOI2.ORG_INFORMATION3),
             TO_NUMBER (NULL))
             OPERATING_UNIT,
          DECODE (
             HOI2.ORG_INFORMATION_CONTEXT,
             'Accounting Information', TO_NUMBER (HOI2.ORG_INFORMATION2),
             NULL)
             LEGAL_ENTITY
     FROM HR_ORGANIZATION_UNITS HOU,
          HR_ORGANIZATION_INFORMATION HOI1,
          HR_ORGANIZATION_INFORMATION HOI2,
          MTL_PARAMETERS MP,
          GL_LEDGERS LGR
    WHERE     HOU.ORGANIZATION_ID = HOI1.ORGANIZATION_ID
          AND HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
          AND HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
          AND HOI1.ORG_INFORMATION1 = 'INV'
          AND HOI1.ORG_INFORMATION2 = 'Y'
          AND (HOI1.ORG_INFORMATION_CONTEXT || '') = 'CLASS'
          AND (HOI2.ORG_INFORMATION_CONTEXT || '') = 'Accounting Information'
          AND TO_NUMBER (
                 DECODE (
                    RTRIM (
                       TRANSLATE (HOI2.ORG_INFORMATION1, '0123456789', ' ')),
                    NULL, HOI2.ORG_INFORMATION1,
                    -99999)) = LGR.LEDGER_ID
          AND LGR.OBJECT_TYPE_CODE = 'L'
          AND NVL (LGR.COMPLETE_FLAG, 'Y') = 'Y';

CREATE OR REPLACE FORCE VIEW APPS.HR_OPERATING_UNITS
(
   BUSINESS_GROUP_ID,
   ORGANIZATION_ID,
   NAME,
   DATE_FROM,
   DATE_TO,
   SHORT_CODE,
   SET_OF_BOOKS_ID,
   DEFAULT_LEGAL_CONTEXT_ID,
   USABLE_FLAG
)
AS
   SELECT O.BUSINESS_GROUP_ID,
          O.ORGANIZATION_ID,
          OTL.NAME,
          O.DATE_FROM,
          O.DATE_TO,
          O3.ORG_INFORMATION5,
          O3.ORG_INFORMATION3,
          O3.ORG_INFORMATION2,
          O3.ORG_INFORMATION6
     FROM HR_ALL_ORGANIZATION_UNITS O,
          HR_ALL_ORGANIZATION_UNITS_TL OTL,
          HR_ORGANIZATION_INFORMATION O2,
          HR_ORGANIZATION_INFORMATION O3
    WHERE     O.ORGANIZATION_ID = O2.ORGANIZATION_ID
          AND O.ORGANIZATION_ID = O3.ORGANIZATION_ID
          AND O2.ORG_INFORMATION_CONTEXT || '' = 'CLASS'
          AND O3.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
          AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
          AND O2.ORG_INFORMATION2 = 'Y'
          AND O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
          AND OTL.LANGUAGE = USERENV ('LANG');

CREATE OR REPLACE PUBLIC SYNONYM HR_OPERATING_UNITS FOR APPS.HR_OPERATING_UNITS;

GRANT SELECT ON APPS.HR_OPERATING_UNITS TO HR_REPORTING_USER;

DROP VIEW APPS.HR_LEGAL_ENTITIES;

/* Formatted on 2015/9/21 下午 10:12:27 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.HR_LEGAL_ENTITIES
(
   BUSINESS_GROUP_ID,
   ORGANIZATION_ID,
   NAME,
   DATE_FROM,
   DATE_TO
)
AS
   SELECT O.BUSINESS_GROUP_ID,
          O.ORGANIZATION_ID,
          OTL.NAME,
          O.DATE_FROM,
          O.DATE_TO
     FROM HR_ORG_UNITS_NO_JOIN         O,
          HR_ALL_ORGANIZATION_UNITS_TL OTL,
          HR_ORGANIZATION_INFORMATION O2
      /* ,HR_ORGANIZATION_INFORMATION O3 */
    WHERE     O.ORGANIZATION_ID = O2.ORGANIZATION_ID
     /* AND O.ORGANIZATION_ID = O3.ORGANIZATION_ID (+)
        AND O2.ORG_INFORMATION_CONTEXT = 'CLASS'
        AND O3.ORG_INFORMATION_CONTEXT (+) = 'Legal Entity Accounting'
        AND O2.ORG_INFORMATION1 = 'HR_LEGAL'
        AND O2.ORG_INFORMATION2 = 'Y' */

        AND O.ORGANIZATION_ID          = OTL.ORGANIZATION_ID
        AND O2.ORG_INFORMATION_CONTEXT = 'CLASS'
        AND O2.ORG_INFORMATION2    = 'Y'
        AND O2.ORG_INFORMATION1 IN ('HR_LEGAL',
                                    'PAR_ENT',
                                    'FR_SOCIETE',
                                    'HR_LEGAL_EMPLOYER')
          AND OTL.LANGUAGE = USERENV ('LANG');

CREATE OR REPLACE PUBLIC SYNONYM HR_LEGAL_ENTITIES FOR APPS.HR_LEGAL_ENTITIES;
GRANT SELECT ON APPS.HR_LEGAL_ENTITIES TO HR_REPORTING_USER;

DROP VIEW APPS.PN_GL_PERIOD_V;

/* Formatted on 2015/9/21 下午 10:50:20 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.PN_GL_PERIOD_V
(
   PERIOD_NAME,
   START_DATE,
   END_DATE
)
AS
     SELECT period_name, start_date, end_date
       FROM gl_period_statuses g, gl_sets_of_books b
      WHERE     b.set_of_books_id = g.set_of_books_id
            AND g.application_id = 101
            AND g.set_of_books_id =
                   pn_mo_cache_utils.get_profile_value ('PN_SET_OF_BOOKS_ID')
            AND g.period_type = b.accounted_period_type
            AND g.adjustment_period_flag = 'N'
            AND closing_status IN ('O', 'F')
   ORDER BY effective_period_num;
---------------------------------
DROP VIEW APPS.HR_ADP_GRE_LIST_V;

/* Formatted on 2015/9/21 下午 10:51:31 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.HR_ADP_GRE_LIST_V
(
   GRE_ID,
   GRE_NAME,
   BUSINESS_GROUP_ID
)
AS
   SELECT hle.organization_id, hle.name, hle.business_group_id
     FROM hr_legal_entities hle;

CREATE OR REPLACE PUBLIC SYNONYM HR_ADP_GRE_LIST_V FOR APPS.HR_ADP_GRE_LIST_V;

DROP VIEW APPS.INVFV_MOVEMENT_PARAMETERS;

/* Formatted on 2015/9/21 下午 10:52:38 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.INVFV_MOVEMENT_PARAMETERS
(
   ENTITY_ORGANIZATION_ID,
   ENTITY_ORGANIZATION_NAME,
   CALENDAR,
   WEIGHT_UOM_CODE,
   CURRENCY_CONVERSION_TYPE,
   "_LA:CURRENCY_CONV_OPTION",
   CATEGORY_SET_ID,
   CATEGORY_SET_NAME,
   TAX_OFFICE_CODE,
   TAX_OFFICE_NAME,
   TAX_OFFICE_LOCATION_ID,
   TAX_OFFICE_LOCATION_CODE,
   TAX_OFFICE_LOCATION_DESC,
   CREATED_BY,
   CREATION_DATE,
   LAST_UPDATED_BY,
   LAST_UPDATE_DATE
)
AS
   SELECT MMP.ENTITY_ORG_ID,
          HLE.NAME,
          MMP.PERIOD_SET_NAME,
          MMP.WEIGHT_UOM_CODE,
          MMP.CONVERSION_TYPE,
          '_LA:MMP.CONVERSION_OPTION:FND_LOOKUPS:MVT_CURR_CONV_OPTION:MEANING', /* LOOKUP */
          MMP.CATEGORY_SET_ID,
          MCS.CATEGORY_SET_NAME,
          MMP.TAX_OFFICE_CODE,
          MMP.TAX_OFFICE_NAME,
          MMP.TAX_OFFICE_LOCATION_ID,
          HL.LOCATION_CODE,
          HL.DESCRIPTION,
          MMP.CREATED_BY,
          MMP.CREATION_DATE,
          MMP.LAST_UPDATED_BY,
          MMP.LAST_UPDATE_DATE
     FROM HR_LOCATIONS HL,
          HR_LEGAL_ENTITIES HLE,
          MTL_CATEGORY_SETS MCS,
          MTL_MOVEMENT_PARAMETERS MMP
    WHERE     HLE.ORGANIZATION_ID = MMP.ENTITY_ORG_ID
          AND MCS.CATEGORY_SET_ID(+) = MMP.CATEGORY_SET_ID
          AND HL.LOCATION_ID(+) = MMP.TAX_OFFICE_LOCATION_ID
   WITH READ ONLY;

DROP VIEW APPS.MTL_MOVEMENT_PARAMETERS_V;

/* Formatted on 2015/9/21 下午 10:53:42 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MTL_MOVEMENT_PARAMETERS_V
(
   ROW_ID,
   ENTITY_ORG_ID,
   ENTITY_NAME,
   ENTITY_BRANCH_REFERENCE,
   PERIOD_SET_NAME,
   CATEGORY_SET_ID,
   CATEGORY_SET_NAME,
   WEIGHT_UOM_CODE,
   TAX_OFFICE_NAME,
   TAX_OFFICE_CODE,
   TAX_OFFICE_LOCATION_ID,
   TAX_OFFICE_LOCATION,
   CONVERSION_OPTION,
   CONVERSION_TYPE,
   DISPLAY_CONVERSION_TYPE,
   LAST_DISPATCH_PERIOD,
   LAST_DISPATCH_ID,
   LAST_ARRIVAL_PERIOD,
   LAST_ARRIVAL_ID,
   LAST_DISPATCH_ADJ_PERIOD,
   LAST_DISPATCH_ADJ_ID,
   LAST_ARRIVAL_ADJ_PERIOD,
   LAST_ARRIVAL_ADJ_ID,
   LAST_UPDATE_DATE,
   LAST_UPDATED_BY,
   CREATION_DATE,
   CREATED_BY,
   LAST_UPDATE_LOGIN,
   ATTRIBUTE_CATEGORY,
   ATTRIBUTE1,
   ATTRIBUTE2,
   ATTRIBUTE3,
   ATTRIBUTE4,
   ATTRIBUTE5,
   ATTRIBUTE6,
   ATTRIBUTE7,
   ATTRIBUTE8,
   ATTRIBUTE9,
   ATTRIBUTE10,
   ATTRIBUTE11,
   ATTRIBUTE12,
   ATTRIBUTE13,
   ATTRIBUTE14,
   ATTRIBUTE15,
   ATTRIBUTE16,
   ATTRIBUTE17,
   ATTRIBUTE18,
   ATTRIBUTE19,
   ATTRIBUTE20,
   ATTRIBUTE21,
   ATTRIBUTE22,
   ATTRIBUTE23,
   ATTRIBUTE24,
   ATTRIBUTE25,
   ATTRIBUTE26,
   ATTRIBUTE27,
   ATTRIBUTE28,
   ATTRIBUTE29,
   ATTRIBUTE30
)
AS
   SELECT M.ROWID,
          M.ENTITY_ORG_ID,
          ENT.NAME,
          M.ENTITY_BRANCH_REFERENCE,
          M.PERIOD_SET_NAME,
          M.CATEGORY_SET_ID,
          CATT.CATEGORY_SET_NAME,
          M.WEIGHT_UOM_CODE,
          M.TAX_OFFICE_NAME,
          M.TAX_OFFICE_CODE,
          M.TAX_OFFICE_LOCATION_ID,
          LOT.LOCATION_CODE,
          M.CONVERSION_OPTION,
          M.CONVERSION_TYPE,
          TYP.USER_CONVERSION_TYPE,
          M.LAST_DISPATCH_PERIOD,
          M.LAST_DISPATCH_ID,
          M.LAST_ARRIVAL_PERIOD,
          M.LAST_ARRIVAL_ID,
          M.LAST_DISPATCH_ADJ_PERIOD,
          M.LAST_DISPATCH_ADJ_ID,
          M.LAST_ARRIVAL_ADJ_PERIOD,
          M.LAST_ARRIVAL_ADJ_ID,
          M.LAST_UPDATE_DATE,
          M.LAST_UPDATED_BY,
          M.CREATION_DATE,
          M.CREATED_BY,
          M.LAST_UPDATE_LOGIN,
          M.ATTRIBUTE_CATEGORY,
          M.ATTRIBUTE1,
          M.ATTRIBUTE2,
          M.ATTRIBUTE3,
          M.ATTRIBUTE4,
          M.ATTRIBUTE5,
          M.ATTRIBUTE6,
          M.ATTRIBUTE7,
          M.ATTRIBUTE8,
          M.ATTRIBUTE9,
          M.ATTRIBUTE10,
          M.ATTRIBUTE11,
          M.ATTRIBUTE12,
          M.ATTRIBUTE13,
          M.ATTRIBUTE14,
          M.ATTRIBUTE15,
          M.ATTRIBUTE16,
          M.ATTRIBUTE17,
          M.ATTRIBUTE18,
          M.ATTRIBUTE19,
          M.ATTRIBUTE20,
          M.ATTRIBUTE21,
          M.ATTRIBUTE22,
          M.ATTRIBUTE23,
          M.ATTRIBUTE24,
          M.ATTRIBUTE25,
          M.ATTRIBUTE26,
          M.ATTRIBUTE27,
          M.ATTRIBUTE28,
          M.ATTRIBUTE29,
          M.ATTRIBUTE30
     FROM MTL_MOVEMENT_PARAMETERS M,
          HR_LEGAL_ENTITIES        ENT,
          HR_LOCATIONS_NO_JOIN    LOC,
          HR_LOCATIONS_ALL_TL       LOT,
          MTL_CATEGORY_SETS_TL      CATT,
          MTL_CATEGORY_SETS_B       CATB,
          GL_DAILY_CONVERSION_TYPES TYP
    WHERE     M.ENTITY_ORG_ID = ENT.ORGANIZATION_ID
          AND M.TAX_OFFICE_LOCATION_ID = LOC.LOCATION_ID(+)
          AND LOC.LOCATION_ID = LOT.LOCATION_ID(+)
          AND DECODE (LOT.LOCATION_ID, NULL, '1', LOT.LANGUAGE) =
              DECODE (LOT.LOCATION_ID, NULL, '1', USERENV ('LANG'))
          AND M.CATEGORY_SET_ID = CATB.CATEGORY_SET_ID(+)
          AND CATB.CATEGORY_SET_ID = CATT.CATEGORY_SET_ID(+)
          AND DECODE (CATT.CATEGORY_SET_ID, NULL, '1', CATT.LANGUAGE) =
              DECODE (CATT.CATEGORY_SET_ID, NULL, '1', USERENV ('LANG'))
          AND M.CONVERSION_TYPE = TYP.CONVERSION_TYPE(+);

DROP VIEW APPS.ECE_MVSTO_HEADERS_V;

/* Formatted on 2015/9/21 下午 10:54:25 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.ECE_MVSTO_HEADERS_V
(
   COMMUNICATION_METHOD,
   TP_TEST_FLAG,
   TP_DOCUMENT_ID,
   TP_DOCUMENT_TYPE,
   TRANSLATOR_CODE,
   TP_LOCATION_CODE_EXT,
   TP_DESCRIPTION,
   TP_REFERENCE_EXT1,
   TP_REFERENCE_EXT2,
   TRANSACTION_DATE,
   TRANSACTION_CONTROL1,
   TRANSACTION_CONTROL2,
   TRANSACTION_CONTROL3,
   DOCUMENT_STANDARD,
   TPH_ATTRIBUTE_CATEGORY,
   TPH_ATTRIBUTE1,
   TPH_ATTRIBUTE2,
   TPH_ATTRIBUTE3,
   TPH_ATTRIBUTE4,
   TPH_ATTRIBUTE5,
   TPH_ATTRIBUTE6,
   TPH_ATTRIBUTE7,
   TPH_ATTRIBUTE8,
   TPH_ATTRIBUTE9,
   TPH_ATTRIBUTE10,
   TPH_ATTRIBUTE11,
   TPH_ATTRIBUTE12,
   TPH_ATTRIBUTE13,
   TPH_ATTRIBUTE14,
   TPH_ATTRIBUTE15,
   TPD_ATTRIBUTE_CATEGORY,
   TPD_ATTRIBUTE1,
   TPD_ATTRIBUTE2,
   TPD_ATTRIBUTE3,
   TPD_ATTRIBUTE4,
   TPD_ATTRIBUTE5,
   PERIOD_NAME,
   EDI_TRANSACTION_REFERENCE,
   EDI_TRANSACTION_DATE,
   MOVEMENT_TYPE,
   REPORT_REFERENCE,
   REPORT_DATE,
   STAT_TYPE,
   USAGE_TYPE,
   MOVEMENT_STATUS,
   ZONE_CODE,
   LEGAL_ENTITY,
   LEGAL_ENTITY_NAME,
   LEGAL_ENTITY_VAT_CODE,
   LEGAL_ENTITY_TAX_NAME,
   ORGANIZATION_ID,
   CUSTOMER_ID,
   ECE_TP_LOC_CODE,
   CUSTOMER_NAME,
   ADDRESS1,
   ADDRESS2,
   ADDRESS3,
   CITY,
   POSTAL_CODE,
   COUNTRY,
   REGION1,
   REGION2,
   REGION3,
   ENTITY_ORG_ID,
   TOTAL_INVOICE_EXT_VALUE,
   TOTAL_SUPPLEMENTARY_UNITS,
   TOTAL_STATISTICAL_VALUE,
   TOTAL_TOTAL_WEIGHT,
   TOTAL_RECORDS,
   MAP_ID
)
AS
   SELECT 'EDI' COMMUNICATION_METHOD,
          ETPD.TEST_FLAG TP_TEST_FLAG,
          'MVSTO' TP_DOCUMENT_ID,
          ETPD.DOCUMENT_TYPE TP_DOCUMENT_TYPE,
          ETPD.TRANSLATOR_CODE TRANSLATOR_CODE,
          HRL.ECE_TP_LOCATION_CODE TP_LOCATION_CODE_EXT,
          ETPH.TP_DESCRIPTION TP_DESCRIPTION,
          ETPH.TP_REFERENCE_EXT1 TP_REFERENCE_EXT1,
          ETPH.TP_REFERENCE_EXT2 TP_REFERENCE_EXT2,
          SYSDATE TRANSACTION_DATE,
          NULL TRANSACTION_CONTROL1,
          NULL TRANSACTION_CONTROL2,
          NULL TRANSACTION_CONTROL3,
          ETPD.DOCUMENT_STANDARD DOCUMENT_STANDARD,
          ETPH.ATTRIBUTE_CATEGORY TPH_ATTRIBUTE_CATEGORY,
          ETPH.ATTRIBUTE1 TPH_ATTRIBUTE1,
          ETPH.ATTRIBUTE2 TPH_ATTRIBUTE2,
          ETPH.ATTRIBUTE3 TPH_ATTRIBUTE3,
          ETPH.ATTRIBUTE4 TPH_ATTRIBUTE4,
          ETPH.ATTRIBUTE5 TPH_ATTRIBUTE5,
          ETPH.ATTRIBUTE6 TPH_ATTRIBUTE6,
          ETPH.ATTRIBUTE7 TPH_ATTRIBUTE7,
          ETPH.ATTRIBUTE8 TPH_ATTRIBUTE8,
          ETPH.ATTRIBUTE9 TPH_ATTRIBUTE9,
          ETPH.ATTRIBUTE10 TPH_ATTRIBUTE10,
          ETPH.ATTRIBUTE11 TPH_ATTRIBUTE11,
          ETPH.ATTRIBUTE12 TPH_ATTRIBUTE12,
          ETPH.ATTRIBUTE13 TPH_ATTRIBUTE13,
          ETPH.ATTRIBUTE14 TPH_ATTRIBUTE14,
          ETPH.ATTRIBUTE15 TPH_ATTRIBUTE15,
          ETPD.ATTRIBUTE_CATEGORY TPD_ATTRIBUTE_CATEGORY,
          ETPD.ATTRIBUTE1 TPD_ATTRIBUTE1,
          ETPD.ATTRIBUTE2 TPD_ATTRIBUTE2,
          ETPD.ATTRIBUTE3 TPD_ATTRIBUTE3,
          ETPD.ATTRIBUTE4 TPD_ATTRIBUTE4,
          ETPD.ATTRIBUTE5 TPD_ATTRIBUTE5,
          EMMSV.PERIOD_NAME PERIOD_NAME,
          EMMSV.EDI_TRANSACTION_REFERENCE EDI_TRANSACTION_REFERENCE,
          EMMSV.EDI_TRANSACTION_DATE EDI_TRANSACTION_DATE,
          EMMSV.MOVEMENT_TYPE MOVEMENT_TYPE,
          EMMSV.REPORT_REFERENCE REPORT_REFERENCE,
          EMMSV.REPORT_DATE REPORT_DATE,
          EMMSV.STAT_TYPE STAT_TYPE,
          EMMSV.USAGE_TYPE USAGE_TYPE,
          EMMSV.MOVEMENT_STATUS MOVEMENT_STATUS,
          EMMSV.ZONE_CODE ZONE_CODE,
          HRE.ORGANIZATION_ID LEGAL_ENTITY,
          HRE.NAME LEGAL_ENTITY_NAME,
          NULL LEGAL_ENTITY_VAT_CODE,
          HRL.TAX_NAME LEGAL_ENTITY_TAX_NAME,
          EMMSV.ORGANIZATION_ID ORGANIZATION_ID,
          HRL.LOCATION_ID CUSTOMER_ID,
          HRL.ECE_TP_LOCATION_CODE ECE_TP_LOC_CODE,
          HRL.LOCATION_CODE CUSTOMER_NAME,
          HRL.ADDRESS_LINE_1 ADDRESS1,
          HRL.ADDRESS_LINE_2 ADDRESS2,
          HRL.ADDRESS_LINE_3 ADDRESS3,
          HRL.TOWN_OR_CITY CITY,
          HRL.POSTAL_CODE POSTAL_CODE,
          HRL.COUNTRY COUNTRY,
          HRL.REGION_1 REGION1,
          HRL.REGION_2 REGION2,
          HRL.REGION_3 REGION3,
          EMMSV.ENTITY_ORG_ID ENTITY_ORG_ID,
          EMMSV.TOTAL_INVOICE_EXT_VALUE TOTAL_INVOICE_EXT_VALUE,
          EMMSV.TOTAL_SUPPLEMENTARY_UNITS TOTAL_SUPPLEMENTARY_UNITS,
          EMMSV.TOTAL_STATISTICAL_VALUE TOTAL_STATISTICAL_VALUE,
          EMMSV.TOTAL_TOTAL_WEIGHT TOTAL_TOTAL_WEIGHT,
          EMMSV.TOTAL_RECORDS TOTAL_RECORDS,
          ETPD.MAP_ID MAP_ID
     FROM ECE_TP_HEADERS    ETPH,
          ECE_TP_DETAILS        ETPD,
          HR_ORGANIZATION_UNITS HOU,
          HR_LOCATIONS          HRL,
          HR_LEGAL_ENTITIES     HRE,
          ECE_MTL_MOV_STAT_V    EMMSV
    WHERE     ETPH.TP_HEADER_ID = ETPD.TP_HEADER_ID
          AND ETPD.DOCUMENT_ID    = 'MVSTO'
          AND ETPD.EDI_FLAG = 'Y'
          AND EMMSV.ENTITY_ORG_ID = HRE.ORGANIZATION_ID
          AND HOU.ORGANIZATION_ID = HRE.ORGANIZATION_ID
          AND HOU.LOCATION_ID     = HRL.LOCATION_ID
          AND HRL.TP_HEADER_ID    = ETPH.TP_HEADER_ID;

DROP VIEW APPS.BIS_LEGAL_ENTITIES_V;

/* Formatted on 2015/9/21 下午 10:55:08 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.BIS_LEGAL_ENTITIES_V
(
   ID,
   VALUE,
   RESPONSIBILITY_ID
)
AS
   SELECT TO_CHAR (L.ORGANIZATION_ID),
          L.NAME,
          B.RESPONSIBILITY_ID
     FROM HR_LEGAL_ENTITIES     L,
          BIS_BUSINESS_GROUPS_V B
    WHERE L.BUSINESS_GROUP_ID = B.ID;

COMMENT ON TABLE APPS.BIS_LEGAL_ENTITIES_V IS '<h5 Class="errorText">***** Warning: Oracle Internal Use Only *****</h5><p> <b class="errorText">Oracle Corporation does not support access to Oracle applications data using this object, except from standard Oracle Applications programs.</b></p><h5>View Type</h5><p>Internal</p>';

The values in below profiles give you the desired data security as explained above. These values are set at site or responsibility level such that once you choose any responsibility, you are restricted to the data pertaining to the above profile value.

HR:Business Group (PER_BUSINESS_GROUP_ID)- implies the business group you are currently attached with.
MO: Operating Unit (ORG_ID)- implies your current operating unit.

Query to find few business groups set up in the instance :	Query to find SOBs set up in the instance :
col name form a30 select business_group_id,name from per_business_groups where lower(name) like '%vision corporation%' order by name /	col sob_name form a30 col chart_of_accounts form a10 select set_of_books_id, name sob_name, chart_of_accounts_id, chart_of_accounts_name, period_set_name calendar_period, accounted_period_type, user_period_type, currency_code from gl_sets_of_books_v where set_of_books_id=1 /
Query to find legal entities associated with a SOB :	Query to find operating units for a legal entity :
select organization_id legal_entity_id, business_group_id, name, date_from, date_to, set_of_books_id, vat_registration_number from hr_legal_entities where set_of_books_id=1 and business_group_id=202 /	col name form a30 select organization_id operating_unit, name, business_group_id, substr(set_of_books_id,1,10), substr(legal_entity_id,1,10), date_from, date_to from hr_operating_units where legal_entity_id=204 /
Query to find inventory organizations for an operating unit :
col organization_name form a30 col organization_code form a10 select organization_id, organization_code, organization_name, (select location_id from hr_all_organization_units ou where od.organization_id=ou.organization_id) location_id, user_definition_enable_date, disable_date, chart_of_accounts_id, inventory_enabled_flag, operating_unit, legal_entity, set_of_books_id, business_group_id from org_organization_definitions od where operating_unit=204 order by organization_code / The location_id found here is referenced in hr_locations table (described later), for detailed address information of this organization.

訂閱：意見 (Atom)

2015年11月19日 星期四